Need Excel Formula help

Forums › Desktop computing › Need Excel Formula help

Bhugz

47 posts

Geek

# 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

1371 posts

Uber Geek

# 605175 4-Apr-2012 16:50

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
64669 posts

Uber Geek

Administrator

Trusted

Geekzone

Lifetime subscriber

# 605176 4-Apr-2012 16:53

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

My technology disclosure | Digital Transformation | Enterprise Content Management | Customer Relationship Management | Data Insights, Business Intelligence and Advanced Analytics

bazzer

3304 posts

Uber Geek

Trusted

# 605179 4-Apr-2012 16:56

Yes, I am an excel guru.

Bee

593 posts

Ultimate Geek

# 605199 4-Apr-2012 17:35

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

# 605244 4-Apr-2012 18:54

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

# 611061 18-Apr-2012 11:32

Is anybody able to help me with the above?

Thanks

D1023319

401 posts

Ultimate Geek

# 611089 18-Apr-2012 12:10

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

# 616378 28-Apr-2012 01:11

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

# 617508 30-Apr-2012 16:34

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

3304 posts

Uber Geek

Trusted

# 617516 30-Apr-2012 16:48

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

NonprayingMantis

6434 posts

Uber Geek

# 617520 30-Apr-2012 17:02

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

# 622751 10-May-2012 10:55

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

# 622770 10-May-2012 11:18

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

# 622778 10-May-2012 11:27

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

# 622884 10-May-2012 14:25

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