Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.

Bhugz

47 posts

Geek

Topic # 100168 4-Apr-2012 16:42

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.

 1 | 2 Next

jbard

1366 posts

Uber Geek

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.

You best post what you need a formula for so people can help?

freitasm

BDFL - Memuneh
62033 posts

Uber Geek

Trusted
Geekzone

Moved to correct forum. You get better/more replies if you use the correct forums.

bazzer

3290 posts

Uber Geek

Trusted

Yes, I am an excel guru.

Bee

593 posts

Ultimate Geek

As am I - I'm sure there are plenty of us here so post the question already :) don't sends pms cos i for one wont reply to that...

Bhugz

47 posts

Geek

Thanks for moving it to the correct area.

Not sure how I can put a sample sheet in this message to explain it better but hopefully the below explanation helps.

The formula I need help with is the MAX/Min formula.

I have to keep the stats for my Indoor cricket team, so I have an Overall Stats sheet, and each game goes into its own sheet.

I have used the following formulas to calculate the total runs, balls faced etc (changing the cell reference as needed SUMPRODUCT(SUMIF(INDIRECT("'"&List&"'!A2:A100"),A5,INDIRECT("'"&List&"'!C2:C100 ")))

Now the problem I have is that for the Max and Min scores I used =max(vlookup(a5,'Game 1'!A1:I9,2,FALSE),VLOOKUP(A5,'Game 2'!A1:I9,2,FALSE)) to get the max and min scores but it gives me a NA error because the players change from game to game.

Bhugz

47 posts

Geek

Is anybody able to help me with the above?

Thanks

D1023319

393 posts

Ultimate Geek

Subscriber

Bhugz: Is anybody able to help me with the above?

Thanks

You seem to have a good grasp of Excel formulas given you are using array formulaes and lookups.
So without seeing your spreadsheet, I think your have a structural problem in the way you are going about your objective maybe through complexity as the min / max functions are straight forward.
It's easy to do when you are nesting so many formulas.

Personally I would use more columns for partial calculations, e.g rather than using Sumproduct() with arrays and Indirect() - so you could then apply the Max / Min formulaes to the partial columns outputs rather than functions such as  Vlookup.

dodgemnz

51 posts

Master Geek

I used to write complex spreadsheets in my last job. After using multiple hidden colums for calculations for some time i got into writing functions that ran in VB on macro sheets. This way you can structure your calculations in smaller routines with naming rather than cell references. Yoiu can add lots of text descriptions at each stage so its much easier to debug. Its very simple to access data on the spreadsheet and return answers back to cells or arrays using this function method to send and recieve data to the macro sheet from individual cells.
One feature that was very handy was to do something on a spreadsheet and you could record the process (Macro) then go and read how the macro was generated, then you can use that as a basis to write your own code. That was very handy when playing with the table and graph options.

Bhugz

47 posts

Geek

https://www.dropbox.com/sh/su23d06expfadat/wFqDhpWkLk here's a link to my spreadsheet in dropbox. Is someone able to take a look at it and figure out what i'm doing wrong and explain it to me?

Thanks heaps.

bazzer

3290 posts

Uber Geek

Trusted

I can try and have a look later in the week, I'm been a bit busy lately, sorry.

NonprayingMantis

6434 posts

Uber Geek

Having a brief look at it, I suspect you might find it a hell of a lot easier to use pivot tables for the whole thing.

do you know how to do that? (If you are using arrays etc, I assume you do)

If not, I can get you started if you PM me.

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

I think will work

for the Min column, just swap the 0 in the ISERROR for some implausibly high number like 500, then as long as the player has played at leats one game it shoudl stop that error from happening.  (If he hasn't played at all then it will return 500)

the pivot table will make it a lot easier to do other analysis later if you want to - I reccomend it.

Bhugz

47 posts

Geek

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.

NonprayingMantis

6434 posts

Uber Geek

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

Bhugz

47 posts

Geek

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

Yeah I was getting a #Name error with your formula. Is there any other way to do it without using pivot tables, like if i have a helper column or something?

Bhugz

47 posts

Geek

How would I create a macro that I could run to get the max values for each player into a separate worksheet if I create a worksheet named Max Values for example.

 1 | 2 Next

Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.

Geekzone Live »

Our community of supporters help make Geekzone possible. Click the button below to join them.