RE: I'm new to Excel,

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

"JLatham" wrote:

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 ***
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 ***, 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 *** 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.

"Drew" wrote:

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.


.

Quantcast