Forum home » Delegate support and help forum » Microsoft Excel Training and help » Adding numerical Values to drop down menus

Active questions (39)
Advanced/consultancy questions (10)
Today's questions
Unanswered questions (4)

Adding numerical Values to drop down menus

The UK's most regular instructor-led training courses.
Training information: microsoft excel course · Excel/courses/london · Microsoft Excel Training London
See also · excel-courses-london · excel courses in london · excel microsoft training

Resolved · Medium Priority · Version 2007

6 posts

ReplyWed 1 Jul 2009, 19:40Delegate Chris said...

Chris has attended:
No courses

Adding numerical Values to drop down menus

Hi, Im trying to find out how to add values to to words contained in a drop down menu.
Each drop down menu will have Yes or No selections (with the exception of a few, come to that if i cant figure it out by then)
The value of Yes and No will be different in each coloum.
A1 Yes value is 1, No value is 0
B1 Yes value us 2, No value is 0
C1 will equal 3 if both Yes, 1 if A1 Yes and B1 No, 0 if both No...You get the gist of it.

The formula i have so far is (Formula placed in C1) =IF(A1="Yes",1,IF(A1="No",0)+(IF(B1="Yes",2,IF(B1="No",0))))
This doesnt add the Values together, what do i need to change to get the values of A1 and B1 to add up?

Also is there a limit to the amount of IF commands you can use on 1 formula (this formula will be a fairly long one if i can find out how to add them together)?

Hope that made some sence, Thanks.

Version	Date	Location	Places available	Book	Next place rate (£)
Excel Advanced 1 day course
Version	Date	Location	Places available	Book	Pay by Card	Pay by Invoice
2003	Wed 17 Mar 2010	Bloomsbury	0	FULL
2007	Fri 19 Mar 2010	Bloomsbury	0	FULL
2003	Wed 24 Mar 2010	Limehouse	7		£212	£217
2007	Fri 26 Mar 2010	Rochester (Hoo)	0	FULL
2007	Mon 29 Mar 2010	Bloomsbury	3		£235	£240
2003	Wed 31 Mar 2010	Southwark	7		£219	£224
Full Schedule: See all 71 Excel Advanced course dates. Bookings currently available until 22nd December 2010.

2,024 posts

ReplyThu 2 Jul 2009, 09:51Trainer Amanda said...

RE: Adding numerical Values to drop down menus

Hello Chris

Thank you for your question and welcome to the forum.

I would suggest having an IF function for each dropdown, so for example in A2, =IF(A1="Yes",1,0)

The formula could then be copied to B2, and then amended slightly =IF(B1="Yes",2,0)

Then create a formula in C1 that adds the results from A2 and B2 together =SUM(A2:B2)

You could hide row 2 if you didn't want the results of the IF functions showing in your spreadsheet, and this would still give you a total in C1.

Would this work for you?

Kind regards
Amanda

6 posts

ReplyThu 2 Jul 2009, 10:37Delegate Chris said...

RE: Adding numerical Values to drop down menus

Hey Amanda, I cant realy use that solution because the next row down will have the same drop down menus in A2 and B2 and result in C2.
Thats why im trying to find out how to add the IF functions together, if it can be done.

2,024 posts

ReplyThu 2 Jul 2009, 11:28Trainer Amanda said...

RE: Adding numerical Values to drop down menus

Hi Chris

How many rows of data do you have with the dropdowns that you will need to add in the same manner?

Kind regards
Amanda

6 posts

ReplyThu 2 Jul 2009, 14:14Delegate Chris said...

RE: Adding numerical Values to drop down menus

Hey Amanda, I have around 16-18 coloums with the drop down menus with each Yes having different numerical values and No will all be 0.
I will using the same formula (if we can figure it out) from about row 8 dwon to 1000+

Thanks.

2,024 posts

ReplyThu 2 Jul 2009, 14:28Trainer Amanda said...

RE: Adding numerical Values to drop down menus

Hi Chris

OK, I understand.

I had another look at the IF function formulas you have written (from your original post), the first problem is that you have too many arguments as part of the IF function. The second problem is how you're trying to add the results of the IF functions - if you nest them within a SUM function then the results of each IF function should be added together.

I've attached an example for you to have a look at.

Kind regards
Amanda

Attached files...

adding IFs.xls

6 posts

ReplyThu 2 Jul 2009, 17:39Delegate Chris said...

RE: Adding numerical Values to drop down menus

Hey Amanda, That was what i was after and after looking at my formula in my first post i can see what i was doing wrong (should have been =(IF(A10="Yes",1,IF(A10="No",0.5)))+(IF(B10="Yes",2,IF(B10="No",1))) if i want to add a differnt value to No) your formula works better in that sense though and is a bit shorter, so i used yours, Thanks.

Next question,
If in one of the cells it has the Yes and No selections again but if Yes selected it times the final amount by 2 and No keeps it at the same value, i wouldnt be able to put that into the formula would I? As it would just cause an error.
So would the best way of doing this be to put the main formula ( =SUM(IF(A1="yes",1,0),IF(B1="yes",2,0)) )in E1 and putting the formula ( =IF(C1="yes",E1*2,E1*1) ) in D1, or would there be a better way of doing it?

A1- Yes=1, No=0
B1 - Yes=2, No=0
C1 - Yes=2xTotal, No=1xTotal
D1 - Total of A1, B1 and C1

Last question for now,
If i put a numerical value above 0.01 in A2 is there forumla that i can use to change A1 to yes while still being able change the numerical value of A2?
Guess it would be kind of a reverse to the above formulas, this is what ive come up with so far, =IF(A2="0",No),IF(A2=">0.01",Yes) (formula placed in A1)
Hope that last one made sense.

Thanks.

2,024 posts

ReplyFri 3 Jul 2009, 10:54Trainer Amanda said...

RE: Adding numerical Values to drop down menus

Hi Chris

I'm assuming that when you mention 'the final amount' you mean the result of the SUM function containing all the IF statements.

You would need to have a separate formula to times this result by 2, as Excel needs to figure out the total first before you can then use that result as part of another calculation. So based on this what you say about creating the two separate formulas makes sense, and I can't think of an alternative way of doing this.

The last IF function you have put together still contains too many arguments within the formula, so Excel wouldn't be able to figure out the answer you require from the formula you've put in.

Excel only requires one test or condition in an IF function; and you are providing two (A2=0 and A2>=0.01).

So if what you are trying to achieve here is to get Excel to display No if the value in A2 is 0, but Yes if any other value is entered into the cell, then the formula would be:

=IF(A2=0,"No","Yes")

By default, if A2 is not equal to 0 then it meets the condition of being another value other than 0 anyway, so Excel just needs you to tell it what to put in if the value in A2 is not 0 (Yes).

I hope this helps.

Amanda

6 posts

ReplySun 5 Jul 2009, 17:44Delegate Chris said...

RE: Adding numerical Values to drop down menus

Seams my problem is putting to many arguments when trying to figure out a formula, have to try and make them a bit more simple.
Thanks for all the help amanda, have all of this sorted now.

Might come back with a few more questions about finding and referencing between sheets, more on that if i get stuck.

Thanks again Amanda

· Do You Really Need Excel VBA Training?
· Why Excel Training (Sometimes) Doesn't Work and What You Can Do About It
· How to Create Better Excel Spreadsheets: Part Two
· Creating Charts with Excel
· Microsoft Excel can ensure you keep on top of your finances

Please browse our web site to find out more about
microsoft excel course and other Microsoft training courses.

Excel tip:

Reconciling a list to correspond with another

May have a list that needs to make but on another sheet one list seems to be out, eg. product list one sheet contains all product and inventory data, while the other contains pricing data. Both need to match with all products but there is more products in one list than the other. To find the disparaging product compare data in the two columns that need to match Make sure that order the same way.
Create another column in the sheet that has the most items and type in the first cell

=Exact(text1,text2) text1 being the cell that you want compared with text2 cell reference. Drag to filldown and your first false will give you for first cells that does not match. Correct insert the row with data in other sheet and continue the process until all the data returns true. Delete the column inserted.

View all Excel hints and tips