Excel 2007 - Database/Formula help needed!

Forums › IT Pro and developers › Excel 2007 - Database/Formula help needed!

Ellipsis

142 posts

Master Geek

#21644 3-May-2008 00:19

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 :-)

1 | 2

2483 posts

Uber Geek

Trusted

#128130 3-May-2008 11:10

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

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

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

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

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

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

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.

Sharesies

Trade NZ and US shares and funds with Sharesies (affiliate link).

manhinli

2483 posts

Uber Geek

Trusted

#128268 3-May-2008 19:52

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!

Find me on Twitter!

I posted 1, 2 x 10^3 times!

Ellipsis

142 posts

Master Geek

#128875 6-May-2008 18:36

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

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

Cheers! :-D

manhinli

2483 posts

Uber Geek

Trusted

#128893 6-May-2008 19:20

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

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

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

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