[mso] Re: [unclassified] Nested IF statement
- From: "David Smart" <smartware@xxxxxxxxxxxxxxx>
- To: <mso@xxxxxxxxxxxxx>
- Date: Sun, 1 Jul 2007 18:13:40 +1000
No, your brackets (parentheses) are OK, but there is one redundant pair -
the one immediately to the left of AND and before the ,0
With your later tests, I don't fully understand what you mean by T5~24 or
T25~49. Perhaps you mean >5 and <= 24, etc? Certainly, Excel won't
understand what you mean either.
The way to do a nested IF to select from a range is to successively check
for "less than" the individual values and to provide the results as you go
along.
I.e. a check against the figures for an individual could be
=IF(B3<5,0%,IF(B3<25,15%,25%))
But it is fairly difficult to combine this with a check for "individual".
You could enclose the whole thing in an IF
=IF(B1="Individual",IF(B3<5,0%,IF(B3<25,15%,25%)), xxx )
where xxx is a similar set of nested IFs for non-individuals.
HOWEVER, there is a limit to the number of nested IF statements you can
have, and this limit is quite small (7) in Excel 2003 and earlier, so you
just won't be able to embed the complexity you need in a nested IF series.
You could use a combination of a single IF for the individual/non-individual
and two VLookUps (one in each of the "then" and "else" parts to grab the
percentages from two tables. That would be easy, as VLookUp is well suited
to selecting from a ranked table such as you have.
Alternatively, you could write a little VBA function to do the whole thing.
It'd be a nice little exercise.
You could possible also arrive at an arithmetic solution - probably using
Boolean arithmetic. They can be fiddly, but can produce good results. For
instance
=(B3>=5)*(B3<25)*15%+(B3>=25)*25%
produces the same as my first IF statement, but without the IFs, so nesting
shouldn't be a problem.
If you have this, and a similar one for the non-individuals, they could go
inside a single IF that checks the type of transaction.
Regards, Dave S
----- Original Message -----
From: "Rachel Jobes" <racheljobes@xxxxxxxxx>
To: <mso@xxxxxxxxxxxxx>
Sent: Saturday, June 30, 2007 10:52 PM
Subject: [unclassified] [mso] Nested IF statement
> Hi
> I have been using Linda's page trying to create a nested IF statement for
> an
> assignment I am doing.
>
> My problem is described below:
>
> A bookshop wishes to calculate a discount for orders based on 2 factors
> (Customer type and number of books)
>
> Customer types are individual, library, school and educational
> institution.
> The breakdown of discounts is different for individuals than for the other
> customer types.
>
> Individual <5 0% T5~24 15% >25 25%
>
> Other
> <5 5% T5~14 10% T15~24 15% T25~49 20% >50 30%
>
> I set up a simple spreadsheet cust type is cell B1 and quantity of books
> is
> cell B3 I am puuting my IF statement in cell B5 which is labelled as
> discount.
>
> The first IF statement step I did works (I think)
> IF((AND(B1="Individual",B3<5)),0,NA) so I figured I could adjust it for
> the
> other steps and add it all together.
>
> Unfortunately I can't seem to get it to work. I haven't done IF
> statements
> before but I think it may be my use of brackets? My other sugestions to
> make the IF statement complete for the individual customer type are
> ((AND(B1="Individual",B3T5~24)),15,NA)
> ((AND(B1="Individual",B3>25)),25,NA)
>
> But then I need to add the same for the other customer types and book
> breakdowns.
>
> Anyone any ideas?
>
> Thanks
> Rache
>
> *************************************************************
> You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
> MicrosoftOffice@xxxxxxxxxxxxxxxx
>
> To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
>
> To Unsubscribe from this group, visit the group's homepage and use the
> dropdown menu at the top. This will allow you to unsubscribe your email
> address or change your email settings to digest or vacation (no mail).
> //www.freelists.org/webpage/mso
>
> To be able to share files with the group, you must join our Yahoo sister
> group. This group will not allow for posting of emails, but will allow
> you to join and share problem files, templates, etc.:
> http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for
> FILE SHARING ONLY.
>
> If you are using Outlook and you see a lot of unnecessary code in your
> email messages, read these instructions that explain why and how to fix
> it:
> http://personal-computer-tutor.com/abc3/v28/greg28.htm
> *************************************************************
>
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, visit the group's homepage and use the dropdown
menu at the top. This will allow you to unsubscribe your email address or
change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to share files with the group, you must join our Yahoo sister group.
This group will not allow for posting of emails, but will allow you to join
and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE
SHARING ONLY.
If you are using Outlook and you see a lot of unnecessary code in your email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************
Other related posts:
