Can't figure out formula error

S

Siper1

The following formula works great until I exceed 500,000
units (ie. 480,000 units = $192,00)

=D19*MIN(--J3,C19)+D20*MIN(MAX(J3-C19,0),C20)+D21*MAX(J3-B21,0)

J3 = 540,000

I could use something as simple as the following link but I need it to
cover multiple periods and want to show the results on a single spreadsheet:

http://cjoint.com/data/ilxdsTVzGk.htm

This is how the table is set up on my spreadsheet -exact columns & rows

Actual:

A B C D
Price
19 Tier 1 0 49,999 0.50
20 Tier 2 50,000 499,999 0.40
21 Tier 3 500,000 0.35


Tiers used as baseline to validate formula is correct (Should = $216,000)

A B C D
Price
19 Tier 1 0 49,999 0.40
20 Tier 2 50,000 499,999 0.40
21 Tier 3 500,000 0.40
 
R

Rick Rothstein \(MVP - VB\)

I'm thinking the formula should be something like this...

=D19*MIN(--J3,C19)+D20*MIN(MAX(J3-C19,0),C20)+D21*MAX(J3-B20-B21,0)

Rick
 
S

Siper1

That worked thanks! For some reason it was $1 off in calculating the higher
tier but that's fine.

Thanks again for your patience. It was a great lesson for me to learn.
 
R

Rick Rothstein \(MVP - VB\)

It may be fine for you, but it bothers me.<g> When I first developed that
formula, the numbers at the top of your tiers end in all zeroes, now they
end in all nines. In looking over everything again, I believe the formula
should be this instead...

=D19*MIN(--J3,C19)+D20*MIN(MAX(J3-C19,0),C20)+D21*MAX(J3-B20-C20,0)

If you are rounding your values to whole dollars, the above change may bring
your calculation in line. Did it?

Rick
 
Top