Hi everyone,
Just wondering if anyone on here is an excel guru and would be able to to help me out with creating a formula that I need because I am a noob with advanced excel formulas.
![]() ![]() ![]() |
|
Bhugz: Hi everyone,
Just wondering if anyone on here is an excel guru and would be able to to help me out with creating a formula that I need because I am a noob with advanced excel formulas.
Please support Geekzone by subscribing, or using one of our referral links: Samsung | AliExpress | Wise | Sharesies | Hatch | GoodSync | Backblaze backup
Doing your best is much more important than being the best.
Bhugz: Is anybody able to help me with the above?
Thanks
ETA: one way around the prpoblems you are having with max/min (without doing a Pivot) would be to nest an IFERROR inside each Vlookup. tis will then check the vlookup and if it shows an error, instead of returning that error, it will return the value you specify.
i.e. for the max score one, if the vlookup returns error, then show 0 that way it will give 0s for each time the player was not present, and the actual score if they were, then the MAX formula should work
=MAX(IFERROR(VLOOKUP(A5,'Game 1'!A:I,2,FALSE),0),IFERROR((VLOOKUP(A5,'Game 2'!A:I,2,FALSE)),0))
in cell I5, then drag and fill
the pivot table will make it a lot easier to do other analysis later if you want to - I reccomend it.
Bhugz:
ETA: one way around the prpoblems you are having with max/min (without doing a Pivot) would be to nest an IFERROR inside each Vlookup. tis will then check the vlookup and if it shows an error, instead of returning that error, it will return the value you specify.
i.e. for the max score one, if the vlookup returns error, then show 0 that way it will give 0s for each time the player was not present, and the actual score if they were, then the MAX formula should work
=MAX(IFERROR(VLOOKUP(A5,'Game 1'!A:I,2,FALSE),0),IFERROR((VLOOKUP(A5,'Game 2'!A:I,2,FALSE)),0))
in cell I5, then drag and fill
the pivot table will make it a lot easier to do other analysis later if you want to - I reccomend it.
Thanks for your suggestion but I actually it need to show the Max and Min score from the games played so I can't have 0s appearing for when a person wasn't playing in a particular game. It would need to show what their max and min score is from the games the person was playing.
It will work as you want. try it out.
The only time it will show a 0 is if the player hasn't played at all in any game (or if their best score actually was 0!)
(and if you want to ignore that, then just setup conditional formatting so a 0 shows as white text or something)
ETA: I just realised it is possible to have a negative score. That screws up the ISERROR part of the calc. sorry. Pivots are your best bet
|
![]() ![]() ![]() |