Can't figure out formula error
 

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

Can't figure out formula error
 

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


"Siper1" wrote in message
...
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

Can't figure out formula error
 

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.


"Rick Rothstein (MVP - VB)" wrote:

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


"Siper1" wrote in message
...
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

Can't figure out formula error
 

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


"Siper1" wrote in message
...
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.


"Rick Rothstein (MVP - VB)" wrote:

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


"Siper1" wrote in message
...
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