To see less ads Register or Login ----- Daily Fantasy Sports games 18+

Using multiple drop down menus to change calculation

A forum to discuss or share knowledge of spreadsheets.
Post Reply
stuboy
Dumbledore
Posts: 5457
Joined: 01 Sep 2010, 12:25
Location: Amsterdam

Using multiple drop down menus to change calculation

Post by stuboy »

I'm trying to create a dynamic price proposal document, so depending on the inputs (from a drop down menu), the cost will change.

For instance, let's say I'm trying to find the cost to a club for hosting a football match. across my x axis I've got Day Game / Night Game as two separate columns

I then have 4 columns along the Y axis for stadium capacity being 80,000 / 50,000/ 30,000/ 5,000

Now I want to have 3 multiple choice questions:
Policing necessary, with drop down of yes/no (the cost of policing being 5000 standard for day game and 8000 for night game for 80K stadium. Obviously the price is different depending on the size of stadium - so although that won't be in the drop down, the price will need to dynamically change.)
Programmes for sell, with drop down of yes/no - with the cost of printing (50p per programme)
Selling beers?, with drop down of "individual/pack of 6" (with cost of buying individual being £1 and pack of 6 being £5)

Once you select your 3 drop down option, the grid between the X and Y axis completes with different costs.

Anyone know how to do this in excel?

User avatar
jpk
Dumbledore
Posts: 5216
Joined: 13 Oct 2005, 18:42
Contact:

Re: Using multiple drop down menus to change calculation

Post by jpk »

sounds like you need to use a formula :P


if I understand correctly your 3 drowdowns will be cells with data validation applied to populate the list of possible values
your result will be a formula that uses those 3 cells with a combination of IF() statements

stuboy
Dumbledore
Posts: 5457
Joined: 01 Sep 2010, 12:25
Location: Amsterdam

Re: Using multiple drop down menus to change calculation

Post by stuboy »

jpk wrote:sounds like you need to use a formula :P


if I understand correctly your 3 drowdowns will be cells with data validation applied to populate the list of possible values
your result will be a formula that uses those 3 cells with a combination of IF() statements
That sounds about correct - but my knowledge of excel is shocking, so I need someone to explain to a layman like me

Thanks

User avatar
jpk
Dumbledore
Posts: 5216
Joined: 13 Oct 2005, 18:42
Contact:

Re: Using multiple drop down menus to change calculation

Post by jpk »

ah well it's not going to be easy to teach the basics of Excel from a series of posts on here.

Maybe you could search for a few youtube vids on Excel formulas and data validation and see how that goes. I could set up an example sheet and send it to you but that seems to be a bit like doing your homework for you and I reckon you'll get a lot more understanding out of learning it for yourself

User avatar
Surprised
FISO Jedi Knight
Posts: 26528
Joined: 13 Oct 2005, 18:32
Location: Home
FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4

Re: Using multiple drop down menus to change calculation

Post by Surprised »

You won't data validation but a drop down box for the 3 multi choice questions as it's only yes or no. Then as jpk says IF statements in the grid to calculate costs which will reference to the yes/no answer.

edit: actually data validation will work fine

View Latest: 1 Day View Your posts
Post Reply

Return to “Spreadsheets”