Excel help - Fantasy Baseball Cafe 2014 Fantasy Baseball Cafe
100% Deposit Bonus for Cafe Members!

Return to General Talk

Excel help

Moderator: Baseball Moderators

Excel help

Postby RynMan » Thu Feb 07, 2008 8:29 am

Hey guys

What I'm trying to do is a little complex.

- I have a range - 3 columns.
- In column A, I have people's names
- Column B has their age
- Column C has their salary

Basically I have another sheet, and I want a particular cell to display the person's salary given two criteria from column A & B.

i.e. IF anywhere in column A (A:A?) = "Jim" AND anywhere in column B (B:B?) = "45", then display "$45,000" (C? it would be on the same row...) in D21

I'd imagine a problem would arise if you had two rows that satisfied the conditions, but that's not the case here.

I thought I could just use an "IF" statement such as =IF(A:A="Jim" & B:B="45", "$45,000") however I think it's having issues that I entered a range. Not to mention I need to ask it to display information from the cell in column C, where the conditions from column A & B are satisfied.

Sorry if this is unclear - can answer questions.
Image
RynMan
Hall of Fame Hero
Hall of Fame Hero

User avatar
CafeholicFantasy ExpertCafe WriterCafe RankerAward-Winning Graphics ExpertMock(ing) DrafterEagle Eye
Posts: 8891
Joined: 4 Feb 2004
Home Cafe: Baseball
Location: Oztailia

Re: Excel help

Postby JTWood » Thu Feb 07, 2008 9:34 am

Unfortunately, you can't use multiple conditions (AND,OR) in an IF statement. You'll have to nest them:

=IF(A:A="Jim","$45,000",if(B:B="45","$45,000","False"))

If that doesn't work for you, a hard workaround is to actually seed data in the subsequent column. So you have an =OR(A1="Jim",B1="45"). You can then key off of the Boolean response in your destination formula with something like a VLOOKUP or whatever's appropriate for your needs. Oh, I always recommend using the actual cell range for a column: A1:A65536, B1:B65536, etc. Some formulas don't like the A:A syntax.

Let me know if that doesn't help you out.

;-D
Image
JTWood
Hall of Fame Hero
Hall of Fame Hero

User avatar
CafeholicCafe WriterEagle EyeWeb Supporter
Posts: 11508
Joined: 22 Jun 2004
Home Cafe: Baseball
Location: Unincorporated Heaven

Re: Excel help

Postby The Artful Dodger » Thu Feb 07, 2008 1:35 pm

Use VLOOKUP. I've had to do a similar thing with names and payments, but this was with two columns, and the condition was to list only people who have made over $20.
Image

It's kinda like the Old Cafe - http://fbc2.freeforums.net
The Artful Dodger
Chief Wikitect
Chief Wikitect

User avatar
CafeholicResponse TeamFantasy ExpertCafe WriterCafe RankerMock(ing) DrafterEagle EyePick 3 Weekly WinnerMatchup Meltdown SurvivorLucky Ladders Weekly Winner
Posts: 21946
(Past Year: 674)
Joined: 3 Feb 2006
Home Cafe: Baseball
Location: Downloading rice

Re: Excel help

Postby Big Pimpin » Thu Feb 07, 2008 2:26 pm

JTWood wrote:Unfortunately, you can't use multiple conditions (AND,OR) in an IF statement. You'll have to nest them:


Sure you can. :-?

If you're trying to fully contain this into one sheet and only display in Column D the results of your search, then you should go row-by-row as opposed to entering a range.

For instance, assuming row 1 holds column headers (name/age/salary), then your formula in D2 would look like this: =IF(AND(A2="Jim",B2=45),C3,"") and the quotes in the last part of the formula just return an empty cell. Then you copy that formula all the way down and only if you're searching for that one person does his salary come up.

Now, if you're trying to search the entire range and just return one entry, then you have to go VLOOKUP. You concatenate Name and Age in a lookup column in A (so insert a column before A). A2 would become =B2&C2. Then you just have an area where you do your lookup and the formula would be =VLOOKUP("Jim"&"45",$A$2:$D$1000,4,0). That pulls Jim's salary when it finds the right one. Of course the 1000 changes to whatever your range actually is. And I'd probably reference cells for the "Jim" and "45" in the lookup so it's much easier to use and update. Let me know if this isn't explained very well. :-D
Big Pimpin
Mod in Retirement
Mod in Retirement

User avatar
EditorCafeholicFantasy ExpertCafe WriterCafe RankerGraphics ExpertMock(ing) DrafterEagle EyeWeb SupporterMatchup Meltdown ChampionPick 3 Weekly WinnerLucky Ladders Weekly Winner
Posts: 13710
(Past Year: 4)
Joined: 20 Apr 2005
Home Cafe: Baseball
Location: Building a metric. And being ignorable and stupid.

Re: Excel help

Postby StlSluggers » Thu Feb 07, 2008 3:20 pm

Big Pimpin wrote:
JTWood wrote:Unfortunately, you can't use multiple conditions (AND,OR) in an IF statement. You'll have to nest them:


Sure you can. :-?

=IF(AND(A2="Jim",B2=45),C3,"")

D'oh! I was thinking of SUMIF. !+)
StlSluggers
Hall of Fame Hero
Hall of Fame Hero

User avatar
CafeholicCafe WriterMock(ing) DrafterWeb Supporter
Posts: 14716
Joined: 24 May 2004
Home Cafe: Baseball
Location: Parking in the gov't bldg @ 7th and Pine. It's only $3.00 on game day!

Re: Excel help

Postby roninmedia » Thu Feb 07, 2008 4:01 pm

Using INDEX and MATCH

Code: Select all
       A          B          C
1    Player      Age      Salary
2    Alex        32       27500
3    Derek       33       18900
4    Manny       36       20000


=INDEX(C2:C4,MATCH(1,IF(A2:A4="Alex",IF(B2:B4=32,1)),0))

Blue: Range in which values, you want to output, in this case, his salary when search results are found
Red: Range in where you're searching for his name, Alex
Green: Range in where you're searching for his age, 33

Note:
Control+Shift+Enter (not just with enter) and copy down.
Try to keep the data in the range to one type, for example, if you include the cell that says "Salary" in the salary column, it will mess up. Text and Numbers don't mix.
Put the name you want to find in " ".
roninmedia
Major League Manager
Major League Manager

User avatar
CafeholicEagle Eye
Posts: 1647
Joined: 16 Jul 2003
Home Cafe: Baseball

Re: Excel help

Postby RynMan » Thu Feb 07, 2008 6:29 pm

roninmedia wrote:Using INDEX and MATCH

Code: Select all
       A          B          C
1    Player      Age      Salary
2    Alex        32       27500
3    Derek       33       18900
4    Manny       36       20000


=INDEX(C2:C4,MATCH(1,IF(A2:A4="Alex",IF(B2:B4=32,1)),0))

Blue: Range in which values, you want to output, in this case, his salary when search results are found
Red: Range in where you're searching for his name, Alex
Green: Range in where you're searching for his age, 33

Note:
Control+Shift+Enter (not just with enter) and copy down.
Try to keep the data in the range to one type, for example, if you include the cell that says "Salary" in the salary column, it will mess up. Text and Numbers don't mix.
Put the name you want to find in " ".


Wow - that was way too easy; :-o

Is there any way to not have "#N/A" in the cell when no value is found? Or is that pushing it?
Image
RynMan
Hall of Fame Hero
Hall of Fame Hero

User avatar
CafeholicFantasy ExpertCafe WriterCafe RankerAward-Winning Graphics ExpertMock(ing) DrafterEagle Eye
Posts: 8891
Joined: 4 Feb 2004
Home Cafe: Baseball
Location: Oztailia

Re: Excel help

Postby bigken117 » Thu Feb 07, 2008 7:47 pm

RynMan wrote:
roninmedia wrote:Using INDEX and MATCH

Code: Select all
       A          B          C
1    Player      Age      Salary
2    Alex        32       27500
3    Derek       33       18900
4    Manny       36       20000


=INDEX(C2:C4,MATCH(1,IF(A2:A4="Alex",IF(B2:B4=32,1)),0))

Blue: Range in which values, you want to output, in this case, his salary when search results are found
Red: Range in where you're searching for his name, Alex
Green: Range in where you're searching for his age, 33

Note:
Control+Shift+Enter (not just with enter) and copy down.
Try to keep the data in the range to one type, for example, if you include the cell that says "Salary" in the salary column, it will mess up. Text and Numbers don't mix.
Put the name you want to find in " ".


Wow - that was way too easy; :-o

Is there any way to not have "#N/A" in the cell when no value is found? Or is that pushing it?


I got this one!

Delete the formula from it ;-)
Image

Jacoby Ellsbury - Sponsored by the Fantasy Baseball Cafe!

Play Pick 3!

Follow the Cafe on Twitter!
Like us on Facebook
bigken117
Bouncer
Bouncer

User avatar
ModeratorCafeholicFantasy ExpertCafe WriterCafe RankerMock(ing) DrafterEagle EyePick 3 Weekly WinnerMatchup Meltdown SurvivorLucky Ladders Weekly Winner
Posts: 12879
(Past Year: 248)
Joined: 15 Feb 2004
Home Cafe: Baseball
Location: posed in an awkward stance

Re: Excel help

Postby bigh0rt » Thu Feb 07, 2008 7:59 pm

RynMan wrote:
roninmedia wrote:Using INDEX and MATCH

Code: Select all
       A          B          C
1    Player      Age      Salary
2    Alex        32       27500
3    Derek       33       18900
4    Manny       36       20000


=INDEX(C2:C4,MATCH(1,IF(A2:A4="Alex",IF(B2:B4=32,1)),0))

Blue: Range in which values, you want to output, in this case, his salary when search results are found
Red: Range in where you're searching for his name, Alex
Green: Range in where you're searching for his age, 33

Note:
Control+Shift+Enter (not just with enter) and copy down.
Try to keep the data in the range to one type, for example, if you include the cell that says "Salary" in the salary column, it will mess up. Text and Numbers don't mix.
Put the name you want to find in " ".


Wow - that was way too easy; :-o

Is there any way to not have "#N/A" in the cell when no value is found? Or is that pushing it?

Can you set up a condition in which if the cell reads #N/A it is replaced with "-" or " "?
Image
bigh0rt
Hall of Fame Hero
Hall of Fame Hero

User avatar
CafeholicCafe WriterGraphics ExpertMock(ing) DrafterEagle EyeInnovative MemberCafe MusketeerWeb SupporterPick 3 Weekly Winner
Posts: 24818
(Past Year: 351)
Joined: 3 Jun 2005
Home Cafe: Baseball
Location: Crowding The Plate

Re: Excel help

Postby Big Pimpin » Thu Feb 07, 2008 8:01 pm

bigh0rt wrote:Can you set up a condition in which if the cell reads #N/A it is replaced with "-" or " "?


Yep...

=IF(ISNA(Formula),"",Formula)

;-D
Big Pimpin
Mod in Retirement
Mod in Retirement

User avatar
EditorCafeholicFantasy ExpertCafe WriterCafe RankerGraphics ExpertMock(ing) DrafterEagle EyeWeb SupporterMatchup Meltdown ChampionPick 3 Weekly WinnerLucky Ladders Weekly Winner
Posts: 13710
(Past Year: 4)
Joined: 20 Apr 2005
Home Cafe: Baseball
Location: Building a metric. And being ignorable and stupid.

Next

Return to General Talk

Who is online

Users browsing this forum: assiquate and 5 guests

Forums Articles & Tips Sleepers Rankings Leagues


Today's Games
Thursday, Oct. 2
(All times are EST, weather icons show forecast for game time)

Detroit at Baltimore
(5:37 pm)
Kansas City at LA Angels
(9:07 pm)

  • Fantasy Baseball
  • Article Submissions
  • Privacy Statement
  • Site Survey 
  • Contact