Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Too many arguments, IF only I could sort it out
excel 97 Hi, I am using the value returned from a drop down list to control which formula is used to return a value in a cell. There are 4 options on the list: Alpha, Beta, Alpha Infinite Depth and Beta Infinite Depth. Here is how I'm trying to compose the argument: =IF(A24="Alpha",H2*SQRT(J2/F2),IF(A24="Beta",H2*SQRT(J2/999)), IF(A24="Alpha Infinite Depth",H2*SQRT(J2/H2), IF(A24="Beta Infinite Depth",H2*SQRT(J2/H2)))) this returns the "too many arguments" error message *DUH! * You say, as you can only compose 3 at a time. Well thats what I don't quite understand, even after reading as much of the excel help, Microsoft Knowledge base and rooting around various threads/tutorials. I can't get my head round how to arrange it. this works: =IF(A24="Alpha",H2*SQRT(J2/F2),IF(A24="Beta",H2*SQRT(J2/999))) this doesn't : =IF(A24="Alpha",H2*SQRT(J2/F2),IF(A24="Beta",H2*SQRT(J2/999)), IF(A24="Alpha Infinite Depth",H2*SQRT(J2/H2))) Can you explain it like you would to a 4 year old? :) here it is laid out: =IF(A24="Alpha",H2*SQRT(J2/F2) IF(A24="Beta",H2*SQRT(J2/999)) IF(A24="Alpha Infinite Depth",H2*SQRT(J2/H2), IF(A24="Beta Infinite Depth",H2*SQRT(J2/H2)))) thanks for any help, the logic of this escapes me. -- G_Chem ------------------------------------------------------------------------ G_Chem's Profile: http://www.excelforum.com/member.php...o&userid=24366 View this thread: http://www.excelforum.com/showthread...hreadid=381215 |
#2
|
|||
|
|||
Hi, I think you just have some of the brackets in the wrong place, try this =IF(A24="Alpha",H2*SQRT(J2/F2),IF(A24="Beta",H2*SQRT(J2/999), IF(A24="Alpha Infinite Depth",H2*SQRT(J2/H2), IF(A24="Beta Infinite Depth",H2*SQRT(J2/H2))))) HTH. -- greg7468 ------------------------------------------------------------------------ greg7468's Profile: http://www.excelforum.com/member.php...fo&userid=9031 View this thread: http://www.excelforum.com/showthread...hreadid=381215 |
#3
|
|||
|
|||
G,
Nesting functions (like these IF's) gets messy. A more manageable way is to use a VLOOKUP to find the result you want. Set up a table somewhere like this: Alpha | =H2*SQRT(J2/F2) Beta | =H2*SQRT(J2/999) Alpha Infinite Depth | etc. Beta Infinite Depth | Now in the original cell (where you want the result, use: =VLOOKUP(A24, Table, 2, FALSE) Table is a reference to the table. It might look like A100:B103. Or it could be a named range. Like "Table." It could be in some hidden rows or column, or even on another sheet. Untested. -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "G_Chem" wrote in message ... excel 97 Hi, I am using the value returned from a drop down list to control which formula is used to return a value in a cell. There are 4 options on the list: Alpha, Beta, Alpha Infinite Depth and Beta Infinite Depth. Here is how I'm trying to compose the argument: =IF(A24="Alpha",H2*SQRT(J2/F2),IF(A24="Beta",H2*SQRT(J2/999)), IF(A24="Alpha Infinite Depth",H2*SQRT(J2/H2), IF(A24="Beta Infinite Depth",H2*SQRT(J2/H2)))) this returns the "too many arguments" error message *DUH! * You say, as you can only compose 3 at a time. Well thats what I don't quite understand, even after reading as much of the excel help, Microsoft Knowledge base and rooting around various threads/tutorials. I can't get my head round how to arrange it. this works: =IF(A24="Alpha",H2*SQRT(J2/F2),IF(A24="Beta",H2*SQRT(J2/999))) this doesn't : =IF(A24="Alpha",H2*SQRT(J2/F2),IF(A24="Beta",H2*SQRT(J2/999)), IF(A24="Alpha Infinite Depth",H2*SQRT(J2/H2))) Can you explain it like you would to a 4 year old? :) here it is laid out: =IF(A24="Alpha",H2*SQRT(J2/F2) IF(A24="Beta",H2*SQRT(J2/999)) IF(A24="Alpha Infinite Depth",H2*SQRT(J2/H2), IF(A24="Beta Infinite Depth",H2*SQRT(J2/H2)))) thanks for any help, the logic of this escapes me. -- G_Chem ------------------------------------------------------------------------ G_Chem's Profile: http://www.excelforum.com/member.php...o&userid=24366 View this thread: http://www.excelforum.com/showthread...hreadid=381215 |
#4
|
|||
|
|||
Try ";" as list separator instead of ","
It works in my worksheet, your formula is true JoyS "G_Chem" wrote: excel 97 Hi, I am using the value returned from a drop down list to control which formula is used to return a value in a cell. There are 4 options on the list: Alpha, Beta, Alpha Infinite Depth and Beta Infinite Depth. Here is how I'm trying to compose the argument: =IF(A24="Alpha",H2*SQRT(J2/F2),IF(A24="Beta",H2*SQRT(J2/999)), IF(A24="Alpha Infinite Depth",H2*SQRT(J2/H2), IF(A24="Beta Infinite Depth",H2*SQRT(J2/H2)))) this returns the "too many arguments" error message *DUH! * You say, as you can only compose 3 at a time. Well thats what I don't quite understand, even after reading as much of the excel help, Microsoft Knowledge base and rooting around various threads/tutorials. I can't get my head round how to arrange it. this works: =IF(A24="Alpha",H2*SQRT(J2/F2),IF(A24="Beta",H2*SQRT(J2/999))) this doesn't : =IF(A24="Alpha",H2*SQRT(J2/F2),IF(A24="Beta",H2*SQRT(J2/999)), IF(A24="Alpha Infinite Depth",H2*SQRT(J2/H2))) Can you explain it like you would to a 4 year old? :) here it is laid out: =IF(A24="Alpha",H2*SQRT(J2/F2) IF(A24="Beta",H2*SQRT(J2/999)) IF(A24="Alpha Infinite Depth",H2*SQRT(J2/H2), IF(A24="Beta Infinite Depth",H2*SQRT(J2/H2)))) thanks for any help, the logic of this escapes me. -- G_Chem ------------------------------------------------------------------------ G_Chem's Profile: http://www.excelforum.com/member.php...o&userid=24366 View this thread: http://www.excelforum.com/showthread...hreadid=381215 |
#5
|
|||
|
|||
G,
Move one of the ")" following "J2/999" to the end of the formula. So... =IF(A24="Alpha",H2*SQRT(J2/F2),IF(A24="Beta",H2*SQRT(J2/999), IF(A24="Alpha Infinite Depth",H2*SQRT(J2/H2),IF(A24="Beta Infinite Depth",H2*SQRT(J2/H2))))) Jim Cone San Francisco, USA "G_Chem" wrote in message ... excel 97 Hi, I am using the value returned from a drop down list to control which formula is used to return a value in a cell. There are 4 options on the list: Alpha, Beta, Alpha Infinite Depth and Beta Infinite Depth. Here is how I'm trying to compose the argument: =IF(A24="Alpha",H2*SQRT(J2/F2),IF(A24="Beta",H2*SQRT(J2/999)), IF(A24="Alpha Infinite Depth",H2*SQRT(J2/H2), IF(A24="Beta Infinite Depth",H2*SQRT(J2/H2)))) this returns the "too many arguments" error message *DUH! * You say, as you can only compose 3 at a time. Well thats what I don't quite understand, even after reading as much of the excel help, Microsoft Knowledge base and rooting around various threads/tutorials. I can't get my head round how to arrange it. this works: =IF(A24="Alpha",H2*SQRT(J2/F2),IF(A24="Beta",H2*SQRT(J2/999))) this doesn't : =IF(A24="Alpha",H2*SQRT(J2/F2),IF(A24="Beta",H2*SQRT(J2/999)), IF(A24="Alpha Infinite Depth",H2*SQRT(J2/H2))) Can you explain it like you would to a 4 year old? :) here it is laid out: =IF(A24="Alpha",H2*SQRT(J2/F2) IF(A24="Beta",H2*SQRT(J2/999)) IF(A24="Alpha Infinite Depth",H2*SQRT(J2/H2), IF(A24="Beta Infinite Depth",H2*SQRT(J2/H2)))) thanks for any help, the logic of this escapes me. G_Chem |
#6
|
|||
|
|||
thanks greg7468 that's done it! :) -- G_Chem ------------------------------------------------------------------------ G_Chem's Profile: http://www.excelforum.com/member.php...o&userid=24366 View this thread: http://www.excelforum.com/showthread...hreadid=381215 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sort by row instead of by column? | Excel Worksheet Functions | |||
sorting more than 3 keys | Excel Discussion (Misc queries) | |||
Sort Ascending button - Excel 2003 | Excel Discussion (Misc queries) | |||
"-" ignored in sort | Excel Discussion (Misc queries) | |||
Data > Sort function amnesia? | Excel Discussion (Misc queries) |