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.




138 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
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!

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?

 
 
 
 


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!

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)



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!

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!

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.

 
 
 
 


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!



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

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!



138 posts

Master Geek


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

Cheers! :-D

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!



138 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

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!



138 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



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.