fatcat
Blogger
Share this content
0
9
1391

more than 7 IF statement

more than 7 IF statement

can any one help to write multiple (more than 7) if statement, want to choose a math for A1 from 8 choices C1:C8   =IF(A1<=B1,C1,IF(A1<=B2,C2,IF(A1<=B3,C3,IF(A1<=B4,C4,IF(A1<=B5,C5,IF(A1<=B6,C6,IF(A1<=B7,C7,&IF(A1<=B8,C8,0). I wanted only choose on result but with &IF it choose a result form C1:C7 plus C8. if I change &IF to +IF it just add the two result together.

Replies

Please login or register to join the discussion.

07th Feb 2013 11:58

Try a VLOOKUP

=IF(ISNA(VLOOKUP(A1,B1:C8,2)),0,VLOOKUP(A1,B1:C8,2))

Thanks (0)
avatar
07th Feb 2013 13:13

And if you have

Excel 2007 or later, George's formula can be further reduced to:

=IFERROR(VLOOKUP(A1,B1:C8,2),0)

 

Thanks (0)
07th Feb 2013 13:20

Thanks Paul

I still think in SuperCalc!

Thanks (0)
avatar
07th Feb 2013 14:16

:-)

I can remember Visicalc!

 

But I do struggle to remember which functions came with which Excel version.

Thanks (0)
avatar
07th Feb 2013 14:47

Personally

I still yearn for the ease of writing macros in the cells of Lotus123 for DOS!

Thanks (0)
07th Feb 2013 14:53

Memories!

Richard Willis wrote:

I still yearn for the ease of writing macros in the cells of Lotus123 for DOS!

Memories! (Writing DOS batch files too . . . )

David

Thanks (0)
07th Feb 2013 15:01

Error!...

... Insert System Disk to continue...[blink][blink][blink]...

Thanks (0)
07th Feb 2013 16:23

Lotus 123

Yes, another vote for Lotus 123 macros in DOS. My consolidation macro used to take about 30 mins to run on a 286. Eeee, they don't know they're born these days!

Thanks (0)
avatar
07th Feb 2013 17:06

I still use Lotus 123

on occasions. A couple of old models that I really can't be bothered to convert.

And I was still running some quite big models with those old Lotus macros until less than 4 years ago. Ahhh the good old days.  :-)

Thanks (0)