Bookmark page
Printable version
Course updatesForum home » Delegate support and help forum » Microsoft Excel Training and help » Adding numerical Values to drop down menus
|
|
|
Bookmark this page|
The UK's most regular 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. |
Adding numerical Values to drop down menus
Resolved · Medium Priority · Version 2007
Reply
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.
For upcoming training course dates see: Pricing & availability
RE: Adding numerical Values to drop down menus
Hello ChrisThank 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
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.
RE: Adding numerical Values to drop down menus
Hi ChrisHow many rows of data do you have with the dropdowns that you will need to add in the same manner?
Kind regards
Amanda
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.
RE: Adding numerical Values to drop down menus
Hi ChrisOK, 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
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.
RE: Adding numerical Values to drop down menus
Hi ChrisI'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
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
|
 |
How To Identify The Most Critical Data In A Large Excel Spreadsheet Excel can store hundreds, thousands, even millions of records, the perfect repository for your company's key data. But if you need to identify a single record or a category of data, wading through that much information could take forever. Fortunately, Excel gives you a range of tools for picking out whatever kind of data you need from your spreadsheets. » Article: Microsoft excel courses london |
» Forum post: Access versions |
» Tip: Notes to Word |
With a view to upgrading, what are the major differences between Access 2000 and 2003?
To have more control over Notes Pages, Send them to Word... Select File/Send To/Microsoft Word. Choose a Page Layout. Select Paste Link if you want your Notes Pages to update in Word when you Save in PowerPoint; Otherwise, select Paste. Click OK. Note for...
