Nesting alternative

jaedmar

Board Regular
Joined
Feb 14, 2011
Messages
60
I have this crazy formula that has 75 if statements, which is too many nested statements.
Any alternatives to this?

=IF(A2=1,G4,IF(A2=2,G4-1,IF(A2=3,G4-2,IF(A2=4,G3-3,IF(A2=5,G4-4,IF(A2=6,G4-5,IF(A2=7,G4-6,IF(A2=8,G4-7,if(a2=9,g4-8,if(a2=10,g4-9,if(a2=11,g4-10,if(a2=12,g4-11,if(a2=13,g4-12,if(a2=14,g4-13,if(a2=15,g4-14,if(a2=15,g4-14,if(a2=16,g4-15,if(a2=17,g4-16,if(a2=18,g4-17,if(a2=19,g4-18,if(a2=20,g4-19,if(a2=21,g4-20,if(a2=22,g4-21,if(a2=23,g4-22,if(a2=24,g4-23,if(a2=25,g4-24,if(a2=26,g4-25,if(a2=27,g4-26,if(a2=28,g4-27,if(a2=29,g4-28,if(a2=30,g4-29,if(a2=31,g4-30,if(a2=32,g4-31,if(a2=32,g4-31,if(a2=33,g4-32,if(a2=34,g4-33,if(a2=35,g4-34,if(a2=36,g4-35,if(a2=37,g4-36,if(a2=38,g4-37,if(a2=39,g4-38,if(a2=40,g4-39,if(a2=41,g4-40,if(a2=42,g4-41,if(a2=43,g4-42,if(a2=44,g4-43,if(a2=45,g4-44,if(a2=46,g4-45,if(a2=47,g4-46,if(a2=48,g4-47,if(a2=49,g4-48,if(a2=50,g4-49,if(a2=51,g4-50,if(a2=52,g4-51,if(a2=53,g4-52,if(a2=54,g4-53,if(a2=55,g4-54,if(a2=56,g4-55,if(a2=57,g4-56,if(a2=58,g4-57,if(a2=59,g4-58,if(a2=60,g4-59,if(a2=61,g4-60,if(a2=62,g4-61,if(a2=63,g4-62,if(a2=64,g4-63,if(a2=65,g4-64,if(a2=66,g4-65,if(a2=67,g4-66,if(a2=68,g4-67,if(a2=69,g4-68,if(a2=70,g4-69,if(a2=71,g4-70,if(a2=72,g4-71,if(a2=73,g4-72,if(a2-74,g4-73,if(a2=75,g4-74)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
Unless I'm missing something....Couldn't your formula just be this?:
=G4-A2+1
 

jaedmar

Board Regular
Joined
Feb 14, 2011
Messages
60
That formual works fine until after I get down to "1". Then it starts producing negative numbers.

How would I write that to leave the cell blank for any result less than "1"?
 

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
Perhaps something like this:
Code:
=MAX(G4-A2+1,0)
or maybe
Code:
=MAX(G4-A2+1,1)
Does that help?
 

bolo

Active Member
Joined
Mar 23, 2002
Messages
423
That formual works fine until after I get down to "1". Then it starts producing negative numbers.

How would I write that to leave the cell blank for any result less than "1"?

Max function doesn't leave blank numbers less than 1 so try

Code:
=if(G4-A2+1<1,"",G4-A2+1)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,961
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top