Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.




47 posts

Geek


# 100168 4-Apr-2012 16:42
Send private message

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.

View this topic in a long page with up to 500 replies per page Create new topic
 1 | 2
1371 posts

Uber Geek


  # 605175 4-Apr-2012 16:50
Send private message

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? 

 
 
 
 


3304 posts

Uber Geek

Trusted

  # 605179 4-Apr-2012 16:56
Send private message

Yes, I am an excel guru.

Bee

593 posts

Ultimate Geek


  # 605199 4-Apr-2012 17:35
Send private message

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...



47 posts

Geek


  # 605244 4-Apr-2012 18:54
Send private message

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.





47 posts

Geek


  # 611061 18-Apr-2012 11:32
Send private message

Is anybody able to help me with the above?

Thanks

401 posts

Ultimate Geek


  # 611089 18-Apr-2012 12:10
Send private message

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. 



 
 
 
 


51 posts

Master Geek


  # 616378 28-Apr-2012 01:11
Send private message

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.
Smile



47 posts

Geek


  # 617508 30-Apr-2012 16:34
Send private message

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.

3304 posts

Uber Geek

Trusted

  # 617516 30-Apr-2012 16:48
Send private message

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

6434 posts

Uber Geek


  # 617520 30-Apr-2012 17:02
Send private message

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.



47 posts

Geek


  # 622751 10-May-2012 10:55
Send private message

 
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.

6434 posts

Uber Geek


  # 622770 10-May-2012 11:18
Send private message

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



47 posts

Geek


  # 622778 10-May-2012 11:27
Send private message



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?



47 posts

Geek


  # 622884 10-May-2012 14:25
Send private message

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
View this topic in a long page with up to 500 replies per page Create new topic



Twitter and LinkedIn »



Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:





News »

Microsoft New Zealand Partner Awards results
Posted 18-Oct-2019 10:18


Logitech introduces new Made for Google keyboard and mouse devices
Posted 16-Oct-2019 13:36


MATTR launches to accelerate decentralised identity
Posted 16-Oct-2019 10:28


Vodafone X-Squad powers up for customers
Posted 16-Oct-2019 08:15


D Link ANZ launches EXO Smart Mesh Wi Fi Routers with McAfee protection
Posted 15-Oct-2019 11:31


Major Japanese retailer partners with smart New Zealand technology IMAGR
Posted 14-Oct-2019 10:29


Ola pioneers one-time passcode feature to fight rideshare fraud
Posted 14-Oct-2019 10:24


Spark Sport new home of NZC matches from 2020
Posted 10-Oct-2019 09:59


Meet Nola, Noel Leeming's new digital employee
Posted 4-Oct-2019 08:07


Registrations for Sprout Accelerator open for 2020 season
Posted 4-Oct-2019 08:02


Teletrac Navman welcomes AI tech leader Jens Meggers as new President
Posted 4-Oct-2019 07:41


Vodafone makes voice of 4G (VoLTE) official
Posted 4-Oct-2019 07:36


2degrees Reaches Milestone of 100,000 Broadband Customers
Posted 1-Oct-2019 09:17


Nokia 1 Plus available in New Zealand from 2nd October
Posted 30-Sep-2019 17:46


Ola integrates Apple Pay as payment method in New Zealand
Posted 25-Sep-2019 09:51



Geekzone Live »

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


Support Geekzone »

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

Support Geezone on PressPatron



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.