How to write formulas for pricing sheet?

[yog]

Hi,
Can you please tell me what formulas to write in Excel2000 for a yes /no decision based on the following in a pricing worksheet?. This pricing sheet is the place for the user to select the car they would like to purchase and their desired option. Eg. in Y? column, put a Y(yes) for chassis type and put Y in the relevant options sections, will produce (base car price + gadgets + safety + luxury options total price in another area of worksheet).
Vehicle Cost
Chassis type Key:
Sedan $24,990 S: Standard Feature
Deluxe $32,990 O: Optional Feature
Wagon $27,990 N: Not Available
Coupe $35,990 C: No Cost Option

Options Summary

Option Price Sedan Deluxe Wagon Coupe
gadgets
CD Player $100 O S O S
CD Changer $400 O O O O
Navigation $600 O O O O
safety
ABS Brakes $800 O S S S
Traction Control $400 N S O S
Side Airbags $900 O S O S
luxury
Cruise Control $150 O S O S
Automatic Tr $2,000 O S O O
Metallic Paint $300 O C O C
Leather Interior $1,500 O O N S

1. Price column of options populate with availability and cost of each option
2. If no chassis selected or if more than 1 type selected, price field of all options read “car”
3. Optional feature in currency form, “standard” in grey, “no cost” in green, and “n/a” in red.

Please help!

 

[BrianB]

Hopefully this will help you on your way. Everyone has their own needs so it is difficult to advise.

The biggest problem is to get started on the right place. In most cases like this the arrangement of information is crucial - and this is quite easy. Think in terms of a "Database" which is really nothing more than a list with column headings in the first row (eg. Chassis, Price, etc) and data underneath. Do not worry too much about pretty formats because it is only used to analyse and filter data - putting the results in other worksheets.

Having this enables the very powerful Excel functions and formulas. You can see that the main structure of Excel is reallydesigned for this.

I have to say that I cannot see how you would do this yet. So ....

Say we have :-
column A for entry of Y for yes.
column B with list of prices
column C with a formula like
=IF(A1="Y",B1,0)

Once entries are made in column A we can put a =SUM() formula at the bottom of column C.

Have a look at Excel Help for functions SUMIF() and COUNTIF()
Also "Database functions"
Also explore items in the Data menu.

Hope this helps.