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.


Ellipsis

142 posts

Master Geek


#21644 3-May-2008 00:19
Send private message

Right... big ask here!

I am doing this spreadsheet to work out my rifle target shooting scores and stuff. I am very new to excel so It might be very obvious questions here.
My Summary page, I want to have a place with my lowest score. I have the Score sorted, but I dont know the formulea to import the date from my "Monday Shooting" page.. if that makes sence. I have drawn a picture to try and clarify :-)




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

Uber Geek

Trusted

  #128130 3-May-2008 11:10
Send private message

Found it!

I looked at the Excel Help for sometime and found the solution.

I'm not sure how you work out the averages and so forth, but here is the spreadsheet (and their formulas):






The second one should be obvious (using MIN and MAX for the values), while the first one is a combination of INDEX and MATCH.

B4 goes like this:
=INDEX($H$13:$H$16, MATCH(C4, $I$13:$I$16, 0), 1)

From left to right:

INDEX is used to reference within an array, $H$13:$H$16 is the array using static referencing, MATCH is used to find the row out of the array for INDEX, C4 is the referenced number (the minimum value), $I$13:$I$16 is the array, 0 is to make sure that the exact value is found, and 1 is the column number required for INDEX.

B5 contains the same formula, but the referenced number for MATCH is C5 (the maximum value)

EDIT: If B4 and B5 turns out with raw numbers, use Date formatting on it.




Find me on Twitter!

I posted 1, 2 x 10^3 times!

hellonearthisman
1819 posts

Uber Geek

Trusted

  #128225 3-May-2008 16:59
Send private message

WOW manhinli what a cool post. Would that also work with google spreadsheet?

 
 
 
 


manhinli
2483 posts

Uber Geek

Trusted

  #128241 3-May-2008 18:03
Send private message

hellonearthisman: WOW manhinli what a cool post. Would that also work with google spreadsheet?

I don't think so, but someone could try (maybe me...)

Google Docs and Spreadsheets is pretty basic but can do quite a lot of stuff - I personally don't use it (that much)




Find me on Twitter!

I posted 1, 2 x 10^3 times!

idlearts
85 posts

Master Geek


  #128247 3-May-2008 18:09
Send private message

could use a vlookup too I guess

=VLOOKUP(MIN(the range of scores),the date and score cols,the col to read the value from 1 in this example,FALSE)



manhinli
2483 posts

Uber Geek

Trusted

  #128254 3-May-2008 18:25
Send private message

I've known HLOOKUP and VLOOKUP for ages, but because the data array is differently ordered and the first column isn't what the score requires (as in we don't enter a date in to check, but a score).

I usually work around problems (unless it gets annoying)




Find me on Twitter!

I posted 1, 2 x 10^3 times!

manhinli
2483 posts

Uber Geek

Trusted

  #128265 3-May-2008 19:09
Send private message

Google Docs is better than I thought - just look at this (Google Account required).




Find me on Twitter!

I posted 1, 2 x 10^3 times!

idlearts
85 posts

Master Geek


  #128267 3-May-2008 19:49
Send private message

manhinli: I've known HLOOKUP and VLOOKUP for ages, but because the data array is differently ordered and the first column isn't what the score requires (as in we don't enter a date in to check, but a score).

I usually work around problems (unless it gets annoying)


Yes your are right, though you cold just swap the look up table cols over in that case.

 
 
 
 


manhinli
2483 posts

Uber Geek

Trusted

  #128268 3-May-2008 19:52
Send private message

idlearts:
manhinli: I've known HLOOKUP and VLOOKUP for ages, but because the data array is differently ordered and the first column isn't what the score requires (as in we don't enter a date in to check, but a score).

I usually work around problems (unless it gets annoying)


Yes your are right, though you cold just swap the look up table cols over in that case.

Yeah, but it's not my data! Smile




Find me on Twitter!

I posted 1, 2 x 10^3 times!

Ellipsis

142 posts

Master Geek


  #128875 6-May-2008 18:36
Send private message

manhinli: Found it!

I looked at the Excel Help for sometime and found the solution.

I'm not sure how you work out the averages and so forth, but here is the spreadsheet (and their formulas):






The second one should be obvious (using MIN and MAX for the values), while the first one is a combination of INDEX and MATCH.

B4 goes like this:
=INDEX($H$13:$H$16, MATCH(C4, $I$13:$I$16, 0), 1)

From left to right:

INDEX is used to reference within an array, $H$13:$H$16 is the array using static referencing, MATCH is used to find the row out of the array for INDEX, C4 is the referenced number (the minimum value), $I$13:$I$16 is the array, 0 is to make sure that the exact value is found, and 1 is the column number required for INDEX.

B5 contains the same formula, but the referenced number for MATCH is C5 (the maximum value)

EDIT: If B4 and B5 turns out with raw numbers, use Date formatting on it.





Thanks for the reply! It looks very useful.

How would I use it on three different pages though? and how would it work for getting the dates from two seperate pages?

manhinli
2483 posts

Uber Geek

Trusted

  #128878 6-May-2008 18:40
Send private message

Ellipsis: How would I use it on three different pages though? and how would it work for getting the dates from two seperate pages?


Use INDIRECT

I'll post something up later when I've got time.





Find me on Twitter!

I posted 1, 2 x 10^3 times!

Ellipsis

142 posts

Master Geek


  #128884 6-May-2008 18:54
Send private message

Cheers! :-D

manhinli
2483 posts

Uber Geek

Trusted

  #128893 6-May-2008 19:20
Send private message

I'm going to upload an Excel .xlsx file up instead...

How do you set up scores and totals on three sheets? Do you need dates on one and scores on another?




Find me on Twitter!

I posted 1, 2 x 10^3 times!

Ellipsis

142 posts

Master Geek


  #128898 6-May-2008 19:33
Send private message

Tell you what...

www.rapide.co.nz/files/Shooting%20Scores.xlsx

There is the excel document I was talking about. :-D

manhinli
2483 posts

Uber Geek

Trusted

  #128905 6-May-2008 19:47
Send private message

That makes it a whole lot easier.

I do have to ask: what's total possible score?




Find me on Twitter!

I posted 1, 2 x 10^3 times!

Ellipsis

142 posts

Master Geek


  #128909 6-May-2008 20:03
Send private message

Oh.

For every score card you do (or every week) there is a possible amount of points of 100.10 (or Score: 100   Inner: 10 in my excel document)
So I want it to tell me, I shot 371 for 4 weeks. But I could have got 400 points.
Hope I was clear.. lol

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





News »

Nanoleaf enhances lighting line with launch of Triangles and Mini Triangles
Posted 17-Oct-2020 20:18


Synology unveils DS1621+ 
Posted 17-Oct-2020 20:12


Ingram Micro introduces FootfallCam to New Zealand channel
Posted 17-Oct-2020 20:06


Dropbox adopts Virtual First working policy
Posted 17-Oct-2020 19:47


OPPO announces Reno4 Series 5G line-up in NZ
Posted 16-Oct-2020 08:52


Microsoft Highway to a Hundred expands to Asia Pacific
Posted 14-Oct-2020 09:34


Spark turns on 5G in Auckland
Posted 14-Oct-2020 09:29


AMD Launches AMD Ryzen 5000 Series Desktop Processors
Posted 9-Oct-2020 10:13


Teletrac Navman launches integrated multi-camera solution for transport and logistics industry
Posted 8-Oct-2020 10:57


Farmside hits 10,000 RBI customers
Posted 7-Oct-2020 15:32


NordVPN starts deploying colocated servers
Posted 7-Oct-2020 09:00


Google introduces Nest Wifi routers in New Zealand
Posted 7-Oct-2020 05:00


Orcon to bundle Google Nest Wifi router with new accounts
Posted 7-Oct-2020 05:00


Epay and Centrapay partner to create digital gift cards
Posted 2-Oct-2020 17:34


Inseego launches 5G MiFi M2000 mobile hotspot
Posted 2-Oct-2020 14:53









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.