IF functions

[Guest]

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???

 

[Roger Govier]

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
(e-mail address removed)> wrote in message
news:[email protected]...

 

[Guest]

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??