Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup value in range, then if that value is something do a calculation.
Hi
I have a list of football results, i,e A B C D Man U 3 0 Bristol Sheff U 6 1 West Ham Wolves 1 2 WBA Leeds 0 0 Arsenal Man Utd 6 0 West Ham On another sheet I want to log if a team has won. A B Team Wins Arsenal in here I was going to put something along the lines of if the team in A3 is in Sheet1 column A then if Sheet 2 Column B Sheet2 Column C class then increase the count by 1. I was also then going to see if Sheet 2 Column D contained Sheet1 column A and if so see if Sheet2 C was greater than Sheet 2 B and add it? Therefore, if you could help. a) What formula would work this out please, I'm getting in a mess with IF statements trying to look up a range for a single value. b) Is there an easier way please? Ideally I'd also check for Draws and losses as well,. Cheers Rich |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup value in range, then if that value is something do a calcul
One way would be like this where "Arsenal" is in sheet2!A2 and your results
data is in sheet1. This formula in sheet2!B2 =SUMPRODUCT(--(sheet1!A$2:A$100=A2),--(sheet1!B$2:B$100sheet1!C$2:C$100))+SUMPRODUCT(--(sheet1!D$2:D$100=A2),--(sheet1!C$2:C$100sheet1!B$2:B$100)) "Richhall" wrote: Hi I have a list of football results, i,e A B C D Man U 3 0 Bristol Sheff U 6 1 West Ham Wolves 1 2 WBA Leeds 0 0 Arsenal Man Utd 6 0 West Ham On another sheet I want to log if a team has won. A B Team Wins Arsenal in here I was going to put something along the lines of if the team in A3 is in Sheet1 column A then if Sheet 2 Column B Sheet2 Column C class then increase the count by 1. I was also then going to see if Sheet 2 Column D contained Sheet1 column A and if so see if Sheet2 C was greater than Sheet 2 B and add it? Therefore, if you could help. a) What formula would work this out please, I'm getting in a mess with IF statements trying to look up a range for a single value. b) Is there an easier way please? Ideally I'd also check for Draws and losses as well,. Cheers Rich |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup value in range, then if that value is something do a calcul
Thanks, what does the -- do? WHy doesn't =IF(Sheet1!A$2:A$100=A2,1,0)
bring back 1 or 0 for me? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup value in range, then if that value is something do a calcul
Thanks for your help, realised how to do it now using VLOOKUP. ta.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
range calculation help | Excel Worksheet Functions | |||
Help with Lookup Calculation | Excel Worksheet Functions | |||
Calculation on lookup | Excel Worksheet Functions | |||
Date Range and calculation | Excel Worksheet Functions | |||
Excel lookup and calculation | Excel Worksheet Functions |