IF functions

  • Thread starter Australian Arrow Propriety Limited
  • Start date
A

Australian Arrow Propriety Limited

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

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]...
 
A

Australian Arrow Propriety Limited

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top