Multi IF function not working

I

IT1Navy

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?
 
P

Pete_UK

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,B21>20),"",INDEX(B3:H3,B21-13)/2)

Hope this helps.

Pete
 
B

Bernard Liengme

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,B21>20),0,INDIRECT(CHAR(B21+52)&"3")/2)

This does away with IF
=AND(B21>13,B21<21)*INDIRECT(CHAR(B21+52)&"3")/2

best wishes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top