help required

[lescor2002]

my excel skills are very basic i can get 2 cells to be added,subtracted etc and result displayed in another cell and thats about it, currently i would like 2 know if it is possible to set this up - example cell a1 qty is 0.45 kg cell b1 min qty is 1.0 kg cell c1 has cost per min qty cell d1 i would be looking for it to display cost but if a1 is less than b1 for d1 to display the min cost, and also if i have a lot of cells to enter the formula is there any way of copying it but so it automatically updates the cell numbers. thanks in advance

[HiArt]

Hi,

yes, this is straight-foreward!

In D1 put "=IF(A1<B1,B1*C1,A1*C1)"

How this works...

The IF function has the format IF(condition, is True then, else is False)

Condition must be something that will resolve to True or False, in this case is the value of cell A1 less than the value of cell B1?

B1*C1 is activated if the condition is true, i.e. when A1 is less than B1, so in your example if the ordered amount is less than the minumum amount.

A1*C1 is only activated if the condition is false, i.e. when A1 is not less than B1, or to put it another way, A1 is greater than or equal to B1.

----
Now, put the cursor in cell D1 and move the mouse to the bottom right-hand corner. When the mouse becomes a +, left-click and drag down to copy the cells.

HTH

Art

[lescor2002]

thanks i tried that but i never thought about it properly but in my example below which is hopefully a bit clearer,sorry the fields aint lined up

item , qty rq , min qty , cost per min qty , cost per item
Butter(kg) , 1.340, 0.500, £1.54, £4.62


qty req field if, is it possible for it to display the input amount ie 1.340 but when it is working out its calculation it rounds it up to the nearest min qty
thanks again

[HiArt]

Ah, OK try this in the cost per item cell:

=ROUNDUP(qty_rq/min_qty,0)*cost_per_min_qty

For example

=ROUNDUP(B1/C1,0)*D1

OK?

Art

[lescor2002]

thanks very much that is exactly what i was needing