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.


Bhugz

48 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
jbard
1377 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? 



freitasm
BDFL - Memuneh
79250 posts

Uber Geek

Administrator
ID Verified
Trusted
Geekzone
Lifetime subscriber

  #605176 4-Apr-2012 16:53
Send private message

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




Please support Geekzone by subscribing, or using one of our referral links: Samsung | AliExpress | Wise | Sharesies | Hatch | GoodSyncBackblaze backup


bazzer
3438 posts

Uber Geek

Trusted

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

Yes, I am an excel guru.



Bee

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




Doing your best is much more important than being the best.


Bhugz

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



Bhugz

48 posts

Geek


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

Is anybody able to help me with the above?

Thanks

D1023319
524 posts

Ultimate Geek

ID Verified

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



 
 
 

Cloud spending continues to surge globally, but most organisations haven’t made the changes necessary to maximise the value and cost-efficiency benefits of their cloud investments. Download the whitepaper From Overspend to Advantage now.
dodgemnz
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

Bhugz

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

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

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

Bhugz

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

NonprayingMantis
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

Bhugz

48 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?

Bhugz

48 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





News and reviews »

Air New Zealand Starts AI adoption with OpenAI
Posted 24-Jul-2025 16:00


eero Pro 7 Review
Posted 23-Jul-2025 12:07


BeeStation Plus Review
Posted 21-Jul-2025 14:21


eero Unveils New Wi-Fi 7 Products in New Zealand
Posted 21-Jul-2025 00:01


WiZ Introduces HDMI Sync Box and other Light Devices
Posted 20-Jul-2025 17:32


RedShield Enhances DDoS and Bot Attack Protection
Posted 20-Jul-2025 17:26


Seagate Ships 30TB Drives
Posted 17-Jul-2025 11:24


Oclean AirPump A10 Water Flosser Review
Posted 13-Jul-2025 11:05


Samsung Galaxy Z Fold7: Raising the Bar for Smartphones
Posted 10-Jul-2025 02:01


Samsung Galaxy Z Flip7 Brings New Edge-To-Edge FlexWindow
Posted 10-Jul-2025 02:01


Epson Launches New AM-C550Z WorkForce Enterprise printer
Posted 9-Jul-2025 18:22


Samsung Releases Smart Monitor M9
Posted 9-Jul-2025 17:46


Nearly Half of Older Kiwis Still Write their Passwords on Paper
Posted 9-Jul-2025 08:42


D-Link 4G+ Cat6 Wi-Fi 6 DWR-933M Mobile Hotspot Review
Posted 1-Jul-2025 11:34


Oppo A5 Series Launches With New Levels of Durability
Posted 30-Jun-2025 10:15









Geekzone Live »

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



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.