Someone wrote this formula but i couldn't make sense of it. Please help me.

First formula, "=vlookup(quantity, base, 2 true) - picks up from quantity
and base table.
Second Formula = vookup(quantity, multipler, 2 true)-picks up from
multipler table
Third formula = if(Quantity 1000, Quantity-I4, if (Quantity=500,
Quantity-I4 ......)))

The issue is "I4 returns $ value from the base table for quantities from
1-20 kgs. "J4 returns Multiplier value in $ from multiplier table for
anything than 20 Kgs. "K4 returns as "I4-Quantity".

For an example; I4 returns $ value for anything below/equals 20 kg (picking
up from the base values) than minuses out any quantity than 20 kg from the
total quantity in question and returns in cell K4. Can you do that?? Dollar
value minus quantity!!! After that K4 multiply by the multiplier(J4) in $
value.

The Final Formula reads : I4*J4 + (K4*J4). Is this possible???

Hi

I think what you are asking is for quantities up to 20 to be charged at
one rate, and anything over 20 to be charged at another rate.
Try adapting the following to your cells as appropriate
With quantity in A1, rate 1 in A2 and rate 2 in A3

=MAX(20,A1)*A2+MIN(0,(A1-20))*A3
--
Regards

Roger Govier


"Australian Arrow Propriety Limited" <Australian Arrow Propriety
wrote in message
...
Someone wrote this formula but i couldn't make sense of it. Please
help me.

First formula, "=vlookup(quantity, base, 2 true) - picks up from
quantity
and base table.
Second Formula = vookup(quantity, multipler, 2 true)-picks up from
multipler table
Third formula = if(Quantity 1000, Quantity-I4, if (Quantity=500,
Quantity-I4 ......)))

The issue is "I4 returns $ value from the base table for quantities
from
1-20 kgs. "J4 returns Multiplier value in $ from multiplier table for
anything than 20 Kgs. "K4 returns as "I4-Quantity".

For an example; I4 returns $ value for anything below/equals 20 kg
(picking
up from the base values) than minuses out any quantity than 20 kg
from the
total quantity in question and returns in cell K4. Can you do that??
Dollar
value minus quantity!!! After that K4 multiply by the multiplier(J4)
in $
value.

The Final Formula reads : I4*J4 + (K4*J4). Is this possible???

Hi Roger,

Thanks for your reply, but you didn't answer my question.

For every quantity, i.e;

Cell A1=vlookup(quantity, base, 2 true); Cell A2 (Vlookup(quantity,
multiplier, 2, true) - Cell A1 carries $ value - Cell A2
carries $ value

Base Multiplier
0.5 Kgs - $5.00 21-40Kgs - $ 13.00 per kg
1.0 Kg - $ 8.00 41-60 Kgs - $ 11.00 per
kg
- 61-80 - $
10.5
- -
- -
- -
20 Kgs - $169.5 9999.0 Kg - $3.2
Max is 20 Kgs

Cell A3 = IF(QUANTITY 20, QUANTITY -A1, QUANTITY 100, QUANTITY -
A1.......Quantity n, quantity -A1))) - Cell A3 carries (-$ value)

SO THE FORMULA READS LIKE THIS (= A1*A2 +(A3*A2))

Example - 100 kgs.

The formula will read (Quantity minus by $value in A1) *A2 ($ value
multiplier) + A3 ($ negative value) * A2)
Where A3 carries this formula- IF(QUANTITY 20, QUANTITY -A1, QUANTITY
100, QUANTITY - A1.......)))

The answer for the above will be (169.5)*$10.5 + (100-169.5)*A2)
( A1) ( A2) (
A3)
=A1*A2 +(A3*A2)
169.5*10.5 + (-$69.5*10.5) = $1050

My question is?? Is this possible??

"Roger Govier" wrote:

Hi

I think what you are asking is for quantities up to 20 to be charged at
one rate, and anything over 20 to be charged at another rate.
Try adapting the following to your cells as appropriate
With quantity in A1, rate 1 in A2 and rate 2 in A3

=MAX(20,A1)*A2+MIN(0,(A1-20))*A3
--
Regards

Roger Govier


"Australian Arrow Propriety Limited" <Australian Arrow Propriety
wrote in message
...
Someone wrote this formula but i couldn't make sense of it. Please
help me.

First formula, "=vlookup(quantity, base, 2 true) - picks up from
quantity
and base table.
Second Formula = vookup(quantity, multipler, 2 true)-picks up from
multipler table
Third formula = if(Quantity 1000, Quantity-I4, if (Quantity=500,
Quantity-I4 ......)))

The issue is "I4 returns $ value from the base table for quantities
from
1-20 kgs. "J4 returns Multiplier value in $ from multiplier table for
anything than 20 Kgs. "K4 returns as "I4-Quantity".

For an example; I4 returns $ value for anything below/equals 20 kg
(picking
up from the base values) than minuses out any quantity than 20 kg
from the
total quantity in question and returns in cell K4. Can you do that??
Dollar
value minus quantity!!! After that K4 multiply by the multiplier(J4)
in $
value.

The Final Formula reads : I4*J4 + (K4*J4). Is this possible???