98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Adding numerical Values to drop down menus | Excel forum
Adding numerical Values to drop down menus | Excel forum
Resolved · Medium Priority · Version 2007
Wed 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.
Thu 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
Thu 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.
Thu 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
Thu 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.
Thu 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.xlsThu 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.
Fri 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
Sun 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
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:No ZerosWant all the zeros in your worksheet to appear blank? Choose Tools/Options, Click on the View Tab, Deselect the Zero Values Option, Click OK. |
