Excel drop down menu and formulas - multi-functional

Question

Hi,

I am attempting to create a spreadsheet that will automatically calculate re-delivery costs by just entering the total cubic, weight and area.

I want to be able to just enter the cubic, weight and area and have the spreadsheet calculate the required details.

There are 12 different areas and each area has it's own base value multiplied by 2 different values to obtain 2 separate totals.

This also only needs to be multiplied by the larger value (either cubic or kgs).

Another friend is using a similar spreadsheet that only requires the cubic and weight, and a drop down menu for the areas.

Example:

Area 1:  Base $12.34     Pickup: 1.4101     Delivery: 1.6587

Area 2:  Base $13.52     Pickup: 1.5265     Delivery: 1.7569 etc

Calculate Cubic: Actual Cubic / 0.333 = Total

Calculate Weight: Actual weight / 1000 = Total

The larger of those 2 totals needs to be multiplied to calculate both Pickup and Delivery separately into 2 different totals.

eg. Area 1 (Base x Pickup) x Total (either cubic or weight depending on which is larger) = Pickup

Area 1 (Base x Delivery) x Total (either cubic or weight depending on which is larger) = Delivery

If Area 1

Cubic 2.57:                   2.57 x 0.333 = 0.85581

Weight 1000 kgs:        1000 / 1000 = 1 (this is larger)

Pickup:                        (12.34 x 1.4101) x 1 = 17.400634

Delivery:                      (12.34 x 1.6587) x 1 = 20.468358

If Area 2

Cubic 8.84:                   8.84 x 0.333 = 2.94372 (this is larger)

Weight 1000 kgs:        1000 / 1000 = 1

Pickup:                        (13.52 x 1.5265) x 2.98372 = 60.7533176016

Delivery:                      (13.52 x 1.7569) x 2.98372 = 69.9230289514

Answer 1

Hi Andrew,

Microsoft Excel is a program that allows you to create and edit spreadsheets. It features calculation, graphing tools, pivot tables, and a macro programming language. You may use it to create formulas and drop-down list. Kindly check these article for more information about Excel formulas and drop-down list.

• Overview of formulas in Excel
• Create a drop-down list

Do let us know should you have further queries.