Nexted if()s are limited to Seven What other functions can I use?

[Dan]

I am attempting to created an ad calendar with the names of the media
placements. The formula's purpose is to chart the expense of the ads by
calendar day in a grid that sums the cost by month. Probem I have is that on
some days I may have more that one magazine I am placing an ad in. This
nested if fuction identifies what media placment is being done for the day
and then looks up the pricing for that media. But I am limited to only 7
nest. What other function could I use? Or does this have to build in VBA?

=IF(B2="SB Top Banner",Pricing!$B$3,IF(B2="SB Buzz",Pricing!$B$2,IF(B2="MC
ND Fax",Pricing!$D$2, IF(B2="SB NewPE",Pricing!$B$4,IF(B2="Direct
Mail",Pricing!$M$2,IF(B2="*"&"PR"&"*",Pricing!$O$4,0))))))

 

[Teethless mama]

Try to use the VLOOKUP or INDEX/MATCH functions

 

[Niek Otten]

http://www.cpearson.com/excel/nested.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I am attempting to created an ad calendar with the names of the media
| placements. The formula's purpose is to chart the expense of the ads by
| calendar day in a grid that sums the cost by month. Probem I have is that on
| some days I may have more that one magazine I am placing an ad in. This
| nested if fuction identifies what media placment is being done for the day
| and then looks up the pricing for that media. But I am limited to only 7
| nest. What other function could I use? Or does this have to build in VBA?
|
| =IF(B2="SB Top Banner",Pricing!$B$3,IF(B2="SB Buzz",Pricing!$B$2,IF(B2="MC
| ND Fax",Pricing!$D$2, IF(B2="SB NewPE",Pricing!$B$4,IF(B2="Direct
| Mail",Pricing!$M$2,IF(B2="*"&"PR"&"*",Pricing!$O$4,0))))))
|

 

[JLatham]

I'm not even certain that he needs anything but a good lookup table to use
VLOOKUP() or HLOOKUP() with. The only odd one in the group is the last one
and that could probably be handled via ISNA() within the lookup formula
itself?

 

[Dan]

Niek,

This looks like it would work for one cell but I am copying the formula for
every day of the month and the totaling [sum()] month. With the values of the
formula dinamically change as I copy the formula from one day to the next?

 

[Dan]

How do I use a cell reference rather that a value for the lookup value
argument?

 

[JLatham]

Going back to your original posting where you were doing IFs on the contents
of B2, you could use
=VLOOKUP(B2,yourlookuptableaddresses,columntoreturninfofrom,0)
Lets say you set up a little table on the same sheet, somewhere way out of
sight or to be hidden later

AA AB
1 SB Top Banner =Pricing!$B$3
2 SB Buzz =Pricing!B$B2
3 MC ND Fax =Pricing!D$2
4 SB NewPE =Pricing!$B$4
5 Direct Mail =Pricing!$M$2

your formula would look like this then:
=VLOOKUP(B2,AA1:AB5,2,0)

The lookup table can be on another sheet, just have to use the sheet name as
part of the lookup table reference as =Vlookup(B2,'SheetName'!AA1:AB5,2,0)

This leaves us with the odd case where you are using wild cards.

If VLOOKUP does not find a match, it returns a #NA error, and you can use
that to your advantage like this:
=IF(ISNA(VLOOKUP(B2,AA1:AB5,2,0)),Pricing!$O$4,VLOOKUP(B2,AA1:AB5,2,0))
In other words, if whatever is in B2 isn't in the list from AA1 to AA5, then
get data from Pricing!$O$4, but if it is in the list, get from the 2nd column
of the table.

 

[Dan]

Thanks for hagging in there with me. I saw my mistake and the ISNA() worked
like a charm.

 

[JLatham]

So it's pretty much working well for you now?

Thanks for hagging in there with me. I saw my mistake and the ISNA() worked
like a charm.