Calculation based on Drop Down lists. (1 Viewer)

[ajb_1976]

Is there a way to perform a calculation based on the value selected from a drop down list?

basically i am trying to monitor the incomings and outgoings of crates, i have a sheet where column A is DATE, B is TYPE (collection / delivery), C is NUMBER OF CRATES.

what i want is to be able to have a running total for column C based on the selection in column B - i.e. if the TYPE is DELIVERY then I add the number in column C to the current total and if it is COLLECTION i subtract it.

Many Thanks

 

[PaulJR]

If I was to have collection/deliveries in column A, and the quantity in column B. I would have a running total in colum C that contains the following formulae:

=IF(A1="delivery",C1+B2,C1-B2)

Paul

 

[ajb_1976]

Thanks Paul, that worked.

What I want to do next is calculate the number of crates in store and for how many days they are there - i.e.

DATE TYPE No of Crates
02/11/2008 Delivery 100
15/11/2008 Delivery 150
22/11/2008 Collection 200

Hence we need to raise charges for 100 crates for 13 days, 250 crates for 7 days and 50 crates for 8 days.

Does this make any sense?! is it possible.............

Thanks.

 

[PaulJR]

If your dates are in column A, then you could place the following formula in, say, cell B3:

=A3-A2

You will then have to set the format for cell B3 to 'number' and set the decimal places to zero. This will give you the number of days between each row.

To calculate the number of crates in store, would a slight variation of the formula I gave you in my earlier post work for you?