FORMULA'S

  • Thread starter Thread starter adnil
  • Start date Start date
A

adnil

I am trying to make a shopping list in B2 i want to put the weight as in 1kg
or 1000grams in C2 I want to put the price per kilo in D2 I want to put the
amount I need say 500grs or half kilo and then in E2 I want the total price.
I have tried a few formula's that I know but this is just not working can
anyone help with this Please. Thanks
 
adnil said:
I am trying to make a shopping list in B2 i want to put the weight as in 1kg
or 1000grams in C2 I want to put the price per kilo in D2 I want to put the
amount I need say 500grs or half kilo and then in E2 I want the total price.
I have tried a few formula's that I know but this is just not working can
anyone help with this Please. Thanks
Click to expand...

I'm not sure exactly what you mean, but I think the get the idea...

Do you want variable Unit of Measure in one cell? e.g.

A B C D E
1 Product UOM PPK Reqd Total
2 Banana 1k $1.10 500g $0.55
3 Apple 1000g $2.00 1k $2.00
4 Peach 1k $3.00 0.5k $1.50

your formula in E will be rather in depth, with many nestings, but i believe
it is possible (certainly in 2007 with the higher limits than previous
versions)

Total =
IF(RIGHT(D2,2)="kg",C2*LEFT(D2,LEN(D2)-2),C2*(LEFT(D2,LEN(D2)-1)/1000))

So, in English, I'm saying

If the last two characters in D2 are kg then (cos the price is per kg);
multiply price by the contents of D2 (less the two characters)
otherwise
(it must be in grams)
first convert the weight to kilos (taking of the trailing g) and multiply
by price

The big must do's for this formula to work, you can only use g or kg.... i
have a solution for how to use other measurements if you need it (e.g.
litres/mls)

HTH
 
Back
Top