How to write formulas for pricing sheet?

yog

New Member
Joined
Jul 25, 2007
Messages
4
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!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.
 
Upvote 0

Forum statistics

Threads
1,222,194
Messages
6,164,508
Members
451,900
Latest member
lamski

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top