Hi
In B5 I have drop down list with two options, AA44 and BB. I want so that when AA44 is choosed, cell G6 does function =B6/5. If BB is choosed, G6 does function =B6/2,5.
User will enter number on B6 so the B6 number will be divided by either 5 or 2,5 by cell G6 depending if AA44 or BB is selected.
Is this possible? I've tried IF and LOOKUP functions but can't get them to work.
Enter this formula in G6:
=IF(B5=AA44,B6/5,B6/2.5)
Excel says the formula contains an error.Originally Posted by majosum
I'm sorry. When I saw AA44, I assumed it was a cell reference to the column AA. Now that I see you only wanted BB as well, try this:
=IF(B5="AA44",B6/5,B6/2.5)
My last formula was checking to see if B5 equaled the value in cell AA44.
Try this
Formula:
Please Login or Register to view this content.
If the Data Validation will not allow other values then
Formula:Please Login or Register to view this content.
Last edited by newdoverman; 11-13-2013 at 07:31 PM.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Still getting an error. Could you try with your Excel?
I get an error with this toocould you try if it works in your Excel?
What exactly is the error that you are getting? Have you checked that the value in B6 is actually a number and not text caused by something other than the number being in the cell. Try entering a new value in B6.
Direct from my Excel.
That workbook you attached is working perfectly for me.
In that case, there is something wrong either in B6 or in the value given in the drop-down list. Open up Data Validation and make sure that your entries there are absolutely correct. Do the same for B6. As you can see from the workbook that I sent you, the formula does work.
That works for me too, but the first one don't. There's a little difference in the last one. You replaced one , with ; and one ; with ,.
Thanks.
By the way, how did you make the drop down list? I made so that I wrote AA44 and BB in a column K and added them to the list that way.
In Data Validation, choose LIST and in the formula field that is at the bottom of the dialogue type in
AA44, BB note that there is no equal sign and the values are separated by a comma.
The semi colon won't work as a separator in formula elements for the most part. There are versions that use the semi colon instead of the comma. The formulae in these examples for this problem don't use semi colons they only use commas.
Although your profile states London, I suspect you are using a Continental version of Excel (French, maybe ?), and in that case you will need to use semicolons ; instead of commas in formulae, as you use a comma to denote the decimal point.
Hope this helps.
Pete
Thanks for the help. I added AA44, BB but it added them on the same line on the drop down list. I changed it to AA44; BB and it worked.
It looks like that is the case. Thanks for the tip.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks