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" <(E-Mail Removed)> wrote in message
news:29F77868-3C5B-476E-B14A-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:401D5279-AD3F-4191-BEC0-(E-Mail Removed)...
>> > 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
>> >
>>
>>
Similar Threads
