Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multi IF function not working
How do I get this type of formula to work without it giving me a "Too many
arguments for this function" error: =IF(B21=14,B3/2,0,IF(B21=15,C3/2,0,IF(B21=16,D3/2,0,IF(B21=17,E3/2,0,IF(B21=18,F3/2,0,IF(B21=19,G3/2,0,IF(B21=20,H3/2,0))))))) Is there another Function I can use? -- IT1 Navy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multi IF function not working
Each of your IFs has one argument too many.
Try leaving out the ,0 -- Kind regards, Niek Otten Microsoft MVP - Excel "IT1Navy" wrote in message ... How do I get this type of formula to work without it giving me a "Too many arguments for this function" error: =IF(B21=14,B3/2,0,IF(B21=15,C3/2,0,IF(B21=16,D3/2,0,IF(B21=17,E3/2,0,IF(B21=18,F3/2,0,IF(B21=19,G3/2,0,IF(B21=20,H3/2,0))))))) Is there another Function I can use? -- IT1 Navy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multi IF function not working
There are two ways to fix your formula (specifically):
=IF(B21=14,B3/2,0)+IF(B21=15,C3/2,0)+IF(B21=16,D3/2,0)+IF (B21=17,E3/2,0)+IF(B21*=18,F3/2,0)+IF(B21=19,G3/2,0)+IF(B21=20,H3/2,0) or: =IF(B21=14,B3/2,IF(B21=15,C3/2,IF(B21=16,D3/2,IF(B21=17,E3/2,IF(B21* =18,F3/2,IF(B21=19,G3/2,IF(B21=20,H3/2,0))))))) However, there are other ways of going about it - here's one way with some error checking: =IF(OR(B21<14,B2120),"",INDEX(B3:H3,B21-13)/2) Hope this helps. Pete On Feb 2, 6:52*pm, IT1Navy wrote: How do I get this type of formula to work without it giving me a "Too many arguments for this function" error: =IF(B21=14,B3/2,0,IF(B21=15,C3/2,0,IF(B21=16,D3/2,0,IF(B21=17,E3/2,0,IF(B21*=18,F3/2,0,IF(B21=19,G3/2,0,IF(B21=20,H3/2,0))))))) Is there another Function I can use? -- IT1 Navy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multi IF function not working
This should read
=IF(B21=14,B3/2,IF(B21=15,C3/2,IF(B21=16,D3/2,IF(B21=17,E3/2,IF(B21=18,F3/2,IF(B21=19,G3/2,IF(B21=20,H3/2,0))))))) Note I have removed all the " ,0 " except the last This works: it has only 7 nested IFs Why not do the division only once =IF(B21=14,B3,IF(B21=15,C3,IF(B21=16,D3,IF(B21=17, E3,IF(B21=18,F3,IF(B21=19,G3,IF(B21=20,H3,0)))))))/2 This is shorted and, unlike the others, can be extended since there is no nesting =IF(OR(B21<14,B2120),0,INDIRECT(CHAR(B21+52)&"3")/2) This does away with IF =AND(B2113,B21<21)*INDIRECT(CHAR(B21+52)&"3")/2 best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "IT1Navy" wrote in message ... How do I get this type of formula to work without it giving me a "Too many arguments for this function" error: =IF(B21=14,B3/2,0,IF(B21=15,C3/2,0,IF(B21=16,D3/2,0,IF(B21=17,E3/2,0,IF(B21=18,F3/2,0,IF(B21=19,G3/2,0,IF(B21=20,H3/2,0))))))) Is there another Function I can use? -- IT1 Navy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Which function/s should be used on a multi conditional formula? | Excel Discussion (Misc queries) | |||
Multi Function for Unique Numbers | Excel Worksheet Functions | |||
multi function cells | Excel Discussion (Misc queries) | |||
Multi-If function | Excel Worksheet Functions | |||
How do I create a multi formula IF function in Excel? | Excel Worksheet Functions |