| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nexted if()s are limited to Seven What other functions can I use?
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)))))) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nexted if()s are limited to Seven What other functions can I use?
Try to use the VLOOKUP or INDEX/MATCH functions
"Dan" wrote: 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)))))) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nexted if()s are limited to Seven What other functions can I use?
http://www.cpearson.com/excel/nested.htm
-- Kind regards, Niek Otten Microsoft MVP - Excel "Dan" wrote in message ... |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)))))) | |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nexted if()s are limited to Seven What other functions can I u
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? "Teethless mama" wrote: Try to use the VLOOKUP or INDEX/MATCH functions "Dan" wrote: 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)))))) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nexted if()s are limited to Seven What other functions can I u
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? "Niek Otten" wrote: http://www.cpearson.com/excel/nested.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "Dan" wrote in message ... |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)))))) | |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nexted if()s are limited to Seven What other functions can I u
How do I use a cell reference rather that a value for the lookup value
argument? "JLatham" wrote: 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? "Teethless mama" wrote: Try to use the VLOOKUP or INDEX/MATCH functions "Dan" wrote: 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)))))) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nexted if()s are limited to Seven What other functions can I u
Going back to your original posting where you were doing IFs on the contents
of B2, you could use =VLOOKUP(B2,yourlookuptableaddresses,columntoretur ninfofrom,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,VLO OKUP(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" wrote: How do I use a cell reference rather that a value for the lookup value argument? "JLatham" wrote: 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? "Teethless mama" wrote: Try to use the VLOOKUP or INDEX/MATCH functions "Dan" wrote: 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)))))) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nexted if()s are limited to Seven What other functions can I u
Thanks for hagging in there with me. I saw my mistake and the ISNA() worked
like a charm. "JLatham" wrote: Going back to your original posting where you were doing IFs on the contents of B2, you could use =VLOOKUP(B2,yourlookuptableaddresses,columntoretur ninfofrom,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,VLO OKUP(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" wrote: How do I use a cell reference rather that a value for the lookup value argument? "JLatham" wrote: 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? "Teethless mama" wrote: Try to use the VLOOKUP or INDEX/MATCH functions "Dan" wrote: 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)))))) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nexted if()s are limited to Seven What other functions can I u
So it's pretty much working well for you now?
"Dan" wrote: Thanks for hagging in there with me. I saw my mistake and the ISNA() worked like a charm. "JLatham" wrote: Going back to your original posting where you were doing IFs on the contents of B2, you could use =VLOOKUP(B2,yourlookuptableaddresses,columntoretur ninfofrom,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,VLO OKUP(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" wrote: How do I use a cell reference rather that a value for the lookup value argument? "JLatham" wrote: 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? "Teethless mama" wrote: Try to use the VLOOKUP or INDEX/MATCH functions "Dan" wrote: 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)))))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match and Sort for two range of data on different worksheets? | New Users to Excel | |||
| Can I get Excel to determine the line curve formula without graph. | Excel Discussion (Misc queries) | |||
| User-defined functions created in Excel 2000 fail in Excel 2003 | Excel Discussion (Misc queries) | |||
| how can I use functions limited to selected records when autofilte | Excel Discussion (Misc queries) | |||
| Limited IF Nested Level functions. | Excel Discussion (Misc queries) | |||
Linear Mode
