Ohio Chatter
Ohio Chatter

Forums

All Threads Serious Business Sports Politics Archive Pick 'ems
Log In Create Account

Excel Formula Help

Home ▸ Archive ▸ Serious Business ▸ Excel Formula Help
ernest_t_bass's avatar

ernest_t_bass

12th Son of the Lama

24,984 posts
Aug 31, 2012 10:41 AM
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"?
Aug 31, 2012 10:41am
derek bomar's avatar

derek bomar

Senior Member

3,722 posts
Aug 31, 2012 10:47 AM
nested Ifs...
Aug 31, 2012 10:47am
derek bomar's avatar

derek bomar

Senior Member

3,722 posts
Aug 31, 2012 10:48 AM
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
Aug 31, 2012 10:48am
Laley23's avatar

Laley23

GOAT

29,506 posts
Aug 31, 2012 10:50 AM
Nested If. Dont use the "" as the second option. Start another If statement. The final one will be "".
Aug 31, 2012 10:50am
Laley23's avatar

Laley23

GOAT

29,506 posts
Aug 31, 2012 10:51 AM
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
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.
Aug 31, 2012 10:51am
ernest_t_bass's avatar

ernest_t_bass

12th Son of the Lama

24,984 posts
Aug 31, 2012 10:51 AM
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
I only need 4. So you're saying:

=if(b1="Psychology","x",if(c1="Psychology","x",if(d1="Psychology","x",if(e1="Psychology","x","")
Aug 31, 2012 10:51am
derek bomar's avatar

derek bomar

Senior Member

3,722 posts
Aug 31, 2012 10:53 AM
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.
might be - i just recently upgraded to the new office so I could be wrong. I know it was like that in 2003.
Aug 31, 2012 10:53am
derek bomar's avatar

derek bomar

Senior Member

3,722 posts
Aug 31, 2012 10:53 AM
ernest_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","")
yes - you'll have to add a few )))) at the end of it though
Aug 31, 2012 10:53am
FatHobbit's avatar

FatHobbit

Senior Member

8,651 posts
Aug 31, 2012 10:54 AM
in that case, can you not do if (b1="psychology" or c1="Psychology" or d1="psychology" or e1="psychology","x","")
Aug 31, 2012 10:54am
derek bomar's avatar

derek bomar

Senior Member

3,722 posts
Aug 31, 2012 10:59 AM
FatHobbit;1258659 wrote:in that case, can you not do if (b1="psychology" or c1="Psychology" or d1="psychology" or e1="psychology","x","")
you could do that as well
Aug 31, 2012 10:59am
ernest_t_bass's avatar

ernest_t_bass

12th Son of the Lama

24,984 posts
Aug 31, 2012 11:16 AM
derek bomar;1258658 wrote:yes - you'll have to add a few )))) at the end of it though
Right. Thanks.
Aug 31, 2012 11:16am
G

gut

Senior Member

15,058 posts
Aug 31, 2012 11:20 AM
Not sure exactly what you are doing, but sometimes it's easier just to create a vlookup table for something like this.
Aug 31, 2012 11:20am
Laley23's avatar

Laley23

GOAT

29,506 posts
Aug 31, 2012 11:21 AM
FatHobbit;1258659 wrote:in that case, can you not do if (b1="psychology" or c1="Psychology" or d1="psychology" or e1="psychology","x","")
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.
Aug 31, 2012 11:21am
O-Trap's avatar

O-Trap

Chief Shenanigans Officer

14,994 posts
Aug 31, 2012 11:23 AM
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.
Yeah, I just did one with 12 nested ifs in 2010. Seems to be working okay so far.
Aug 31, 2012 11:23am
G

gut

Senior Member

15,058 posts
Aug 31, 2012 11:34 AM
O-Trap;1258684 wrote:Yeah, I just did one with 12 nested ifs in 2010. Seems to be working okay so far.
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.
Aug 31, 2012 11:34am
O-Trap's avatar

O-Trap

Chief Shenanigans Officer

14,994 posts
Aug 31, 2012 11:36 AM
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.
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.
Aug 31, 2012 11:36am
gorocks99's avatar

gorocks99

Senior Member

10,760 posts
Aug 31, 2012 12:01 PM
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.
Aug 31, 2012 12:01pm
FatHobbit's avatar

FatHobbit

Senior Member

8,651 posts
Aug 31, 2012 12:46 PM
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.
I might have screwed up the syntax.
Aug 31, 2012 12:46pm