Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
Hi,
I have created this formula:
=IF(A3<>"",IF(B2="Xn (log)",LOG(A3),IF(A3<>"",IF(B2="1/x",1/A3,IF(A3<>"",IF(B2="x2",A3*A3,IF(A3<>"",IF(B2="x3",A3*A3*A3))))))))
Basically it checks to see if there is a value in A3 and then looks at what has been selected from a drop down menu in B2 and depending on what is in B2 it returns either the log of A3, 1/A3, A3xA3, A3xA3xA3. This seems to work fine. However, when I add a fourth option as shown at the end below it always says there is an error. I can't figure out why ?
=IF(A3<>"",IF(B2="Xn (log)",LOG(A3),IF(A3<>"",IF(B2="1/x",1/A3,IF(A3<>"",IF(B2="x2",A3*A3,IF(A3<>"",IF(B2="x3",A3*A3*A3,IF(A3<>"",IF(B2="sqrt x",SQRT(A3))))))))))
Can any one help ?
You can't have more than 7 nested IF's in an Excel formula.
There are many way's around this. One way is to use VLOOKUP on the range that created the dropdown.
For example, let's say your dropdown list was created from C1:C2 as follows
1/x
x-squaredIn D1:D2 put
=1/A3
=A3*A3Then your formmula would be:
=IF(A3<>"",VLOOKUP(B2,C1:D2,2,0),"")
Much less typing and very easy to change choices/results.
0 
If you're into Boolean algebra (and a lot of typing) you can use the following formula. It works because Excel treats FALSE as 0 and TRUE as 1.
e.g.
=(A=A) equals TRUE so =(A=A)*6 = 6=(A=B) equal FALSE so =(A=B)*6 = 0
=IF(A3<>"",(B2="Xn (log)")*LOG(A3)+(B2="1/x")*1/A3+(B2="x2")*(A3*A3)+(B2="x3")*(A3*A3*A3)+(B2="sqrt x")*SQRT(A3),"")
0
I hope you choose the the VLOOKUP method.
The Boolean method was more for fun, if you enjoy playing with the "hidden" side of Excel.
I've often wondered if the programmers at MS tried to write the app so it could do things like the Boolean algebra solution to your question or if they just came up with all the different functions and it was the users who said "Hey! Look what I just made it do!"
0
Hi
At first I worked through your Boolean Algebra formula,
tried it and it did work - very well in fact. However, then I
reverted back to my original formula and managed to
remove some of the IF statements and got that to work.
The problem with the VLOOKUP was that I created the
drop down from the Validate and list function and the
selection of the drop down was intended to dictate the
value in a cell range rather than just Cell A3. Anyway here
is what i used and copied through out the range and
seems to be ok?=IF(A3=0,"",IF($B$1="log
x",LOG(A3),IF($B$1="1/x",1/A3,IF($B$1="x2",A3*A3,IF($B
$1="x3",A3*A3*A3,IF($B$1="sqrt x",SQRT(A3),""))))))Thanks again
0
I'm confused.
re: "I created the drop down from the Validate and list function"
So did I. I'm not sure why that is a problem. You can either use that same list as the first column of the lookup_array with the corresponding formulae in the next column over or recreate the list somewhere else with the corresponding formulae in the next column over.
re: the selection of the drop down was intended to dictate the value in a cell range rather than just Cell A3.
So what prevents you from copying a VLOOKUP formula down through a range?
=IF(A3<>"",VLOOKUP($B$2,$C$1:$D$2,2,0),"")
I'm not pushing my solution on you, I just don't see how the reasons you gave prevent it's use.
0
Hi,
I guess I wanted to avoid having a lookup table - and wasn't
sure how to do this and hide the look up from the
spreadsheet user. Sure there are ways to do this, but the
formula I posted back seems to do the job without the need
for a table.Thanks all the same.
0
re: wasn't sure how to do this and hide the look up from the spreadsheet user.
Again, not pushing my solution - although the reason I like it is that it makes changes much easier - but here is one way to hide the lookup_array from your users. Even if you don't use this now, the concepts might be useful for some other project.
We're going to use Named Ranges and Hidden Sheets...
1 - Create your lookup_array on a different sheet. The first column will be used for your DropDown list, the second column will contain the corresponding formluae.
2 - Select the first column and click in the NameBox - the box above Column A that shows the active cell.
3 - Type in a name for that range, e.g. MyDropDownList. Make sure you hit Enter. If you just click outside the box, the name won't be saved.
4 - Select both columns and give that range a name e.g. MyLookUpTable.
5 - Back on the other sheet, click the cell where you want your DropDown list and use =MyDropDownList as the Data...Validation...List source.
6 - For your VLOOKUP formula, use:
=IF(A3<>"",VLOOKUP($B$2,MyLookUpTable,2,0),"")
and drag down.7 - Finally, select the sheet where the list source/look_up array is and use Format...Sheet...Hide.
The users would have to do a bit of hunting to find the table. You could even add some protection to the lookup sheet to prevent them from seeing the table even if they unhid the sheet.
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
