ernest_t_bass
Posts: 24,984
Aug 31, 2012 10:41am
I can't figure this out. Formula goes in A1. I want A1 to display "x" if a term shows up in a certain field, one of four fields. I can get one field, but can't get the other 3. Here is what I have:
=if(b1="Psychology","x","")
How do I write that formula to include c1, d1, and e1 = "Psychology"?
=if(b1="Psychology","x","")
How do I write that formula to include c1, d1, and e1 = "Psychology"?
derek bomar
Posts: 3,722
Aug 31, 2012 10:47am
nested Ifs...
derek bomar
Posts: 3,722
Aug 31, 2012 10:48am
so where your first formulas goes "x","", make it say "x",if(... and then start over with the next cell you're trying to look at. You can only have i believe 7 nested Ifs
Laley23
Posts: 29,506
Aug 31, 2012 10:50am
Nested If. Dont use the "" as the second option. Start another If statement. The final one will be "".
Laley23
Posts: 29,506
Aug 31, 2012 10:51am
I think thats old. I have a formula for my timesheets/work and I have about 20 Ifs for calculating my money earned.derek bomar;1258645 wrote:so where your first formulas goes "x","", make it say "x",if(... and then start over with the next cell you're trying to look at. You can only have i believe 7 nested Ifs
Also made a player pickem and had about a 45 nested If formula. Then again, I dont use excel, I use Google Docs...so that might be a reason.
ernest_t_bass
Posts: 24,984
Aug 31, 2012 10:51am
I only need 4. So you're saying:derek bomar;1258645 wrote:so where your first formulas goes "x","", make it say "x",if(... and then start over with the next cell you're trying to look at. You can only have i believe 7 nested Ifs
=if(b1="Psychology","x",if(c1="Psychology","x",if(d1="Psychology","x",if(e1="Psychology","x","")
derek bomar
Posts: 3,722
Aug 31, 2012 10:53am
might be - i just recently upgraded to the new office so I could be wrong. I know it was like that in 2003.Laley23;1258652 wrote:I think thats old. I have a formula for my timesheets/work and I have about 20 Ifs for calculating my money earned.
Also made a player pickem and had about a 45 nested If formula. Then again, I dont use excel, I use Google Docs...so that might be a reason.
derek bomar
Posts: 3,722
Aug 31, 2012 10:53am
yes - you'll have to add a few )))) at the end of it thoughernest_t_bass;1258653 wrote:I only need 4. So you're saying:
=if(b1="Psychology","x",if(c1="Psychology","x",if(d1="Psychology","x",if(e1="Psychology","x","")
FatHobbit
Posts: 8,651
Aug 31, 2012 10:54am
in that case, can you not do if (b1="psychology" or c1="Psychology" or d1="psychology" or e1="psychology","x","")
derek bomar
Posts: 3,722
Aug 31, 2012 10:59am
you could do that as wellFatHobbit;1258659 wrote:in that case, can you not do if (b1="psychology" or c1="Psychology" or d1="psychology" or e1="psychology","x","")
ernest_t_bass
Posts: 24,984
Aug 31, 2012 11:16am
Right. Thanks.derek bomar;1258658 wrote:yes - you'll have to add a few )))) at the end of it though
G
gut
Posts: 15,058
Aug 31, 2012 11:20am
Not sure exactly what you are doing, but sometimes it's easier just to create a vlookup table for something like this.
Laley23
Posts: 29,506
Aug 31, 2012 11:21am
That does work also. I like If statements, cause I think they are easier to change 1 part of it and not have the re-write the whole formula. Just the IF that you need to change.FatHobbit;1258659 wrote:in that case, can you not do if (b1="psychology" or c1="Psychology" or d1="psychology" or e1="psychology","x","")
O-Trap
Posts: 14,994
Aug 31, 2012 11:23am
Yeah, I just did one with 12 nested ifs in 2010. Seems to be working okay so far.derek bomar;1258656 wrote:might be - i just recently upgraded to the new office so I could be wrong. I know it was like that in 2003.
G
gut
Posts: 15,058
Aug 31, 2012 11:34am
Hahahaha, the bane of programmers.O-Trap;1258684 wrote:Yeah, I just did one with 12 nested ifs in 2010. Seems to be working okay so far.
One way to guarantee no one (including yourself) can understand what the spreadsheet is doing is to have like 4 cells in a row passing a result based on a 12-step nested if.
O-Trap
Posts: 14,994
Aug 31, 2012 11:36am
Eh, it's all stat tracking from what the data programmers already spit out, so they don't have to deal with it. It's for my own reports.gut;1258698 wrote:Hahahaha, the bane of programmers.
One way to guarantee no one (including yourself) can understand what the spreadsheet is doing is to have like 4 cells in a row passing a result based on a 12-step nested if.
gorocks99
Posts: 10,760
Aug 31, 2012 12:01pm
If you just need to know if one of the cells has "psychology", why not an "or"?
=if(or(b1="psychology",c1="psychology",etc.),x,"")
oops. looks like fathobbit already suggested that.
=if(or(b1="psychology",c1="psychology",etc.),x,"")
oops. looks like fathobbit already suggested that.
FatHobbit
Posts: 8,651
Aug 31, 2012 12:46pm
I might have screwed up the syntax.gorocks99;1258727 wrote:If you just need to know if one of the cells has "psychology", why not an "or"?
=if(or(b1="psychology",c1="psychology",etc.),x,"")
oops. looks like fathobbit already suggested that.