LOL - I was struggling there too Don, thinking huh, what was wrong with the
formula offered?
Ekser, in case you can't find the other post, here is what I posted wrt Don's
formula:-
What you have is a number pattern, and because that pattern can be identified it
is usually quite easy to cater for all the options without resorting to long
formulas with nested IFs.
Your example
=IF(J19=2,G19*100,IF(J19=3,G19*125,IF(J19=4,G19*150,IF(J19=5,G19*175,IF(J19>5,G19*200)))))
It appears that every time you add 1 to J19, the value you multiply G19 by goes
up by 25. This means that if we take 50 out of every part of that equation
(Don't worry, we'll put it back), you would get something like
=IF(J19=2,G19*50,IF(J19=3,G19*75,IF(J19=4,G19*100,IF(J19=5,G19*125,IF(J19>5,G19*200)))))
The pattern is now more obvious in that a 2 in J19 gets you 2 lots of 25 (50), a
3 gets you 3 lots of 25 (75), a 4 gets you 4 lots of 25 (100) etc. This means
that you could now use that value of J19 to replace all these hardwired values:-
=IF(J19=2,G19*(J19*25),IF(J19=3,G19*(J19*25),IF(J19=4,G19*(J19*25),IF(J19=5,G19*(J19*25),.......
I'll ignore the last part for a moment.
This can be condensed to
=G19*(J19*25) as it will give you the same answers for all but the last part,
and oh by the way, nearly forgot about your 50
=G19*(50+J19*25)
This now works for all your values EXCEPT that last one where you have said
anything GREATER than 5 gets a max factor of 200, so what we now do is wrap what
we already had in a MIN statement that says give me the smallest value of either
the result of the formula we already have, OR 200. This means that for your
smaller values it will take the value from the formula, but if for example J19
was 8, the formula would return =G19*(50+8*25) which = G19*(250). The MIN
formula however says that it will use the lower of the formula result OR 200,
and so hence the
=G19*MIN(200,50+25*J19)
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03
----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------
Don Guillett said:
Perhaps you didn't see my original post or Ken Wright's post very early in
this thread?
