Help needed with formulas

[LittleAths Volunteer]

Hi
I need help with some formulas in a worksheet which records, over 16 weeks,
all performances of an athlete while highlighting any improvements on their
previous best and awards 1 point,and also collects 2 points for each weeks
attendance. The total of which will be used for trophies and certificates.

Wk1 Wk2 Wk3 Wk4 PB
100m 22.36 22.31 23.28 23.07 22.31
longjump 1.68 1.68 1.85 DNF 1.85
Totals
Improve 0 1 1 0 4
Attend 2 2 2 2 8

The improvement of wk4 only equals an improvement if it was better than
personal best not if it was just an improvement on the previous week.Is this
the best way to explain what I am trying to do or is there somewhere i can
send the worksheet to get formulas added.

 

[Sandy Mann]

To get the same Improve result, (but not the same SUM(), try:

=IF(AND(ISNUMBER(C2),C2<MIN($B$2:B2))+AND(ISNUMBER(C3),C3>MAX($B$3:B3))>0,1,0)

for Wk2 and copy across. I assume that Improve for Wk1 will always be 0

For the Attend try in Wk1:

=IF(COUNT(B2:B3)>0,2,0)

and copy across

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[MartinW]

Hi,

It's sounds like you are not trying to learn Excel, as such,
you just want a working spreadsheet that will make your life
a little easier, and maybe pick up some handy tips while you
are doing it.

If that is the case, go to this site, http://www.savefile.com/
and upload a copy of your spreadsheet. Then post the link
to the site back here, along with a complete explanation
of what you would like it to do.

Myself, or someone else will be more than happy to create
a spreadsheet for you.

I might not be back for a little while, so any one else who
want's to jump in, please feel free to do so.

HTH
Martin

 

[LittleAths Volunteer]

Hi Martin,

Many thanks for your assistance and I have now loaded the file to the
following link in the hopes someone may be able to help.

http://www.savefile.com/files/1845150

I have simple knowledge of Excel learnt when I attended a course for the
very basics but thatwas many years ago, unfortunately I am now in a remote
rural area and access to these types of courses is not available.
--
Little Athletics Volunteer
Promoting Family,Fun and Fitness

Hi,

It's sounds like you are not trying to learn Excel, as such,
you just want a working spreadsheet that will make your life
a little easier, and maybe pick up some handy tips while you
are doing it.

If that is the case, go to this site, http://www.savefile.com/
and upload a copy of your spreadsheet. Then post the link
to the site back here, along with a complete explanation
of what you would like it to do.

Myself, or someone else will be more than happy to create
a spreadsheet for you.

I might not be back for a little while, so any one else who
want's to jump in, please feel free to do so.

HTH
Martin

 

[LittleAths Volunteer]

Hi Sandy Mann,

Thanks for your response and I will have a crack at doing this but i suspect
I have to change some details and being none the wiser I hope I get them
right.
--
Little Athletics Volunteer
Promoting Family,Fun and Fitness

To get the same Improve result, (but not the same SUM(), try:

=IF(AND(ISNUMBER(C2),C2<MIN($B$2:B2))+AND(ISNUMBER(C3),C3>MAX($B$3:B3))>0,1,0)

for Wk2 and copy across. I assume that Improve for Wk1 will always be 0

For the Attend try in Wk1:

=IF(COUNT(B2:B3)>0,2,0)

and copy across

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[MartinW]

Hi,

I use XL2000 and can't open your file which is in .xlsx format
and requires XL2007 to open.

If you save to an earlier format I will be able to open it.

In case you don't know how to do that, all you need to do is
go to File>Save As and in the Files of Type dropdown select the
option for 'Microsoft Excel 1997-2000/5.0 Workbook(.xls)'
That may not be exactly correct in your 2007 version but it
will be something very similar.

P.S. I live in Western Queensland, Australia and know full well
the problems of living in a remote area. The internet makes a
huge difference to my life.

Regards
Martin

Hi Martin,

Many thanks for your assistance and I have now loaded the file to the
following link in the hopes someone may be able to help.

http://www.savefile.com/files/1845150

I have simple knowledge of Excel learnt when I attended a course for the
very basics but thatwas many years ago, unfortunately I am now in a remote
rural area and access to these types of courses is not available.

 

[LittleAths Volunteer]

Evening Martin,

I am in Southern South A but from Qld and I very much miss home!! Anyway
here is the link to the compatible document.

http://www.savefile.com/files/1845513

Again thanks ever so much for your assistance, it will free up so much time
and save much in travel costs etc.

 

[Sandy Mann]

Looking at your uploaded file the *times* that you have for the events are a
mixture of all different dates and times and so cannot be used as presented.

I have altered the formatting of the times to h:m:s.00 therefore 10.15
seconds have to be entered as 0:0:10.15 and 1 minute 15.25 seconds as
0:1:15.25

Here is a link to my Demo file:

http://www.savefile.com/files/1846090

I used a *helper* table to hold the Max/Min times/distances. This table can
be hidden to make the sheet more presentable.

I have also altered the Conditional Formatting.

I have assumed that you want 1 for every improvement in any sport to that
date, (ie if there is an improvement in two sports then you still just get
1), and 2 for ever day of attendance in any sport. If you want 1 for
*every* improvement then remove the MIN() function around the Improvement
formula.

This is just a Demo to give you ideas.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Hi Sandy Mann,

Thanks for your response and I will have a crack at doing this but i
suspect
I have to change some details and being none the wiser I hope I get them
right.

 

[MartinW]

Hi,

I think this should do what you want. The calculations are done in
hidden helper columns off to the right. Please note the different
input above and below the double border.

This is a first draft so please do a lot of testing to see if it
covers all of your criteria.
http://www.savefile.com/files/1846296

HTH
Martin

 

[LittleAths Volunteer]

Thanks for your much appreciated time and effort in helping me with this. I
have also looked at what you have done and taken notes so if I try anything
else like this in future I now have a better understanding of how the
formulas work.