I'm new to Excel,

[Guest]

and am self taught with minimal experience. I was wondering if someone knew
of a better way to perform this formula:

=IF(SUM(E5/F5<95),IF(SUM(E5+100)/(F5+1)>=95,1,IF(SUM(E5+200)/(F5+2)>=95,2,IF(SUM(E5+300)/(F5+3)>=95,3,IF(SUM(E5+400)/(F5+4)>=95,4,IF(SUM(E5+500)/(F5+5)>=95,5,IF(SUM(E5+600)/(F5+6)>=95,6,GET TO WORK)))))),0)


What I'd like to do is remove the ',get to work' statement and add more if
funtions but of course excel won't allw me to nest more than 7. Is there
another function I could be using?? I do not know macros, YET, so while I'm
not against it, I would like to use an entry I could explain to someone.

 

[Guest]

I'm not sure I've translated what you are looking for correctly, but I think
I have.

What I did was set up a 2-column table at J5 and K5 running down the sheet
J5 formula is
=IF(($E$5+(K5*100))/($F$5+(ROW(J5)-ROW($J$5)))>=95,"YES","NO")
then in K5 I entered the number zero.
Then I extended both formulas down the sheet, making sure that the values in
K incremented by 1, as 0, 1, 2, 3...

I used 3200 in E5 and 50 in F5 and had to extend that table a long way -
down past row 315 (which were the formula first reaches 95) - I went down to
row 358 with it.

Then in the cell where you have your big nested IF formula, you could put
this
=IF(E5/F5<95,IF(ISNA(VLOOKUP("YES",J5:K358,2,0)),0,VLOOKUP("YES",J5:K358,2,0)),0)

that will basically look down the table to the first YES entry and return
the number next to it (310 in my test case). It only looks when E5/F5 is <
95, and only returns a non-zero value if some value in the K column caused
"YES" to be displayed.

No reason that the table has to be on the same sheet with your E5/F5 and IF
cells.

The J5:K358 entries in the IF formula above must include the starting and
ending cell addresses of the table you create for this.

Either formula can return a #DIV/0 error if F5 is zero. To prevent that,
you can use these instead:

In the IF formula location
=IF(ISERR(IF(E5/F5<95,IF(ISNA(VLOOKUP("YES",J5:K358,2,0)),0,VLOOKUP("YES",J5:K358,2,0)),0)),0,IF(E5/F5<95,IF(ISNA(VLOOKUP("YES",J5:K358,2,0)),0,VLOOKUP("YES",J5:K358,2,0)),0))

For the first formula in the first cell in the table, use this, which will
prevent a #DIV/0 error in the first cell and prevent erroneous results in the
rest:
=IF(F5=0,"NO",IF(($E$5+(K5*100))/($F$5+(ROW(J5)-ROW($J$5)))>=95,"YES","NO"))

Hope I interpreted what you needed correctly.

 

[Guest]

Oops, made a mistake in the second version of the formula that should go into
the first cell in the table (J5 in my setup). That should be:
=IF($F$5=0,"NO",IF(($E$5+(K5*100))/($F$5+(ROW(J5)-ROW($J$5)))>=95,"YES","NO"))

The change from =IF(F5=0, ... to =IF($F$5=0,... makes a whole lot of
difference in the accuracy of the results in the table!

 

[Guest]

Thank you JLatham for your help. I'm not sure if I wille be able to explain
that
or replicate that if I needed too. It looks good from what I can tell. I
guess I should have given more info about what I'm doing. I'm creating a
personal stat sheet for my work so that I know what my stats are at the end
each day instead of the 15th and end of the month, because my company tells
us too late in the month what our stats are to have a chnace to correct it if
it's wrong. So, all that said, I only need the formula to go thru probably
15, because any number greater than 15 would be unrealistic in terms of
recovering my stats to the proper level. With this info make the formula less
complicated, from my point of view that is??

 

[Guest]

As long as this is just something you want to keep handy to give you a hint
as to status, you could probably deal with things like the #DIV/0 showing up
and could just use the initial formulas without the error checking. That
would keep them looking simpler and more 'understandable'. Plus you'd only
have to create a table 15 rows long.

Another option is to use two cells to get more "IF" statements tested. You
could set up a two more to pick up where the first left off, checking for
7...13 and for 14, 15 in the last one.

Back to the option I offered last night, and to simplify it some - it was at
the end of a long day and I'd tried a couple of methods and quite frankly,
mixed two methods together at one place, which probably adds to the
confusion. My apologies.

In the 'table', which can start at any row when done properly (not as I did
in the formula earlier) set things up like below, I'll show columns K and J
again:

J
K
1 =IF($F$5=0,"NO",IF(($E$5+(K1*100))/($F$5+K1)>=95,"YES","NO")) 0
2 =IF($F$5=0,"NO",IF(($E$5+(K2*100))/($F$5+K2)>=95,"YES","NO")) 1
3 =IF($F$5=0,"NO",IF(($E$5+(K3*100))/($F$5+K3)>=95,"YES","NO")) 2

and continue down to a value of 15 in column K. Then back in the cell where
you have your IF statements, just put this formula:
=IF(E5/F5<95,VLOOKUP("YES",J1:K358,2,0),0)
sometimes you will have #DIV/0 shown, at other times, as when no value in
the table comes up to be >=95, you'll have #N/A shown.

If you can live with that, those are the simplest formulas to use to get it
done without using several cells with nested IFs in them.

 

[Guest]

Just wanted to say thank you for your help JLatham.
I really appreciate it.

 

[Guest]

You're quite welcome. Sorry for the false start.

Odd thing is that I came up with a way to do it with a one-column table and
using MATCH() where you had the IF() statement. Saves a column. If
interested, just reply to this and I'll post the alternate solution also. No
better or worse than what we've done already, just a little different and
saves one column.

 

[Guest]

No need for any working columns, try this one:
=SUMPRODUCT( MAX( ( ( (E5 + ( ( ROW($1:$50) - 1 ) * 100 ) ) / ( F5 +
ROW($1:$50) - 1 ) ) < 95 ) * ROW($1:$50) ) )

Adjust the last number ($50) of the 3 "ROW($1:$50)" to the desire
"precision". 50 is equivalent to 50 nested IF() levels in your original
formula...

 

[Guest]

Drew,
Take a look at the solution offered by PapaDos below. He's an absolute
master of the Excel function and I have no doubt that anything he puts up
will work first time out of the bag!