>>HELP<< Microsoft Excel FORMULA's.....

[BOMI]

I need help.

I have a excel spread sheet at work and need help with some formulas.

I have say one name, that person can get 5 different results depending on how many push ups and sit ups etc they do.

There are 4 different exercises that will give me a score.


So if Bloggs does 25 push ups and 35 XXXX and 15 ZZZZ then 65 VVVV he will get say a score of 4, more of each exercise will get a higher number, 5 being the highest.

Hope this is making sense LOL.

Basicaly I want itto tell me what score out of 1 to 5 people get without having to sit there for 2 hrs doing it.


Any help would be great!!!

Troy :cheers:

[35R]

so basically you have something that looks like this?



Person ExterciseA ExerciseB ExerciseC ... ...

John 25 35 45
Peter 35 45 55
Paul 45 55 65



And for each exercise they will yield a score, and then add them all up for the final score per person?

Troy perhaps attach the spreadsheet you are working on and i can help you out.

[hsvredsled]

I need help.

I have a excel spread sheet at work and need help with some formulas.

I have say one name, that person can get 5 different results depending on how many push ups and sit ups etc they do.

There are 4 different exercises that will give me a score.


So if Bloggs does 25 push ups and 35 XXXX and 15 ZZZZ then 65 VVVV he will get say a score of 4, more of each exercise will get a higher number, 5 being the highest.

Hope this is making sense LOL.

Basicaly I want itto tell me what score out of 1 to 5 people get without having to sit there for 2 hrs doing it.


Any help would be great!!!

Troy :cheers:


I think I get it.

You want the totals of the exercises to add up to a figure which equates to a 1,2,3,4,or 5. This can be done by using 'if' statements.

But, are you going to score the push ups etc equally or apply a weighting? For example, 10 push ups may be equal to 2 chin ups? Weighting can be easily applied by putting *2 OR *3 or whatever in the cell depending on the weighting you decide.

[hsvredsled]

Ok,

Here is your formula IF statement.

=IF(H15>100,5,IF(H15>90,4,IF(H15>80,3,IF(H15>70,2,IF(H15>60,1,)))))

H15 is the cell that your totals should be in....but you can change it if you like.

In this scenario, a total of greater than 100 will yield a 5, total of 90 a 4 and so on.

You can copy this into a cell and change 100,90,80,70 etc only!!

Or....flick me a PM with your email addy and send me the XL workbook and I will organise for you.


:woot:

[BOMI]

so basically you have something that looks like this?



Person ExterciseA ExerciseB ExerciseC ... ...

John 25 35 45
Peter 35 45 55
Paul 45 55 65



And for each exercise they will yield a score, and then add them all up for the final score per person?

Troy perhaps attach the spreadsheet you are working on and i can help you out.

Its at work mate, ill email it to you, cheers man :cheers:


I think I get it.

You want the totals of the exercises to add up to a figure which equates to a 1,2,3,4,or 5. This can be done by using 'if' statements.

But, are you going to score the push ups etc equally or apply a weighting? For example, 10 push ups may be equal to 2 chin ups? Weighting can be easily applied by putting *2 OR *3 or whatever in the cell depending on the weighting you decide.


Hmmmm, not add up, lets say for fitness you have 1 to 5 incentive levels.

More you do of something or faster the better score you get.


EG


P/U's ..... S/U's ....... Heaves ... Beep test ..... Score
20 .......... 80 .......... 12 ........... 8.5 ............... 1
25 ........... 90 .......... 14 .......... 10.2 .............. 2
35 ........... 95 .......... 18 .......... 11.5 .............. 3
45 .......... 100 .......... 20 .......... 12 .............. 4
60 .......... 120 .......... 25 .......... 12.8 ................ 5


Without excel doing it automaticaly i have to go through 90 peoples results and see what they did then work out what that score would be.


You must get all at or above the scores above to get 1 2 3 4 or 5.

If you do 20 P/U's, 80 S/U's, 12 Heaves but blow the beep test and get say 6.2 you wont get a score, you will just get a pass.

All the figures are above the pass mark, so basicaly its an incentive to not just pass but to get a 5 or 1 at least.



Cheers guys.


Troy :cheers:

[hsvredsled]

You must get all at or above the scores above to get 1 2 3 4 or 5.

If you do 20 P/U's, 80 S/U's, 12 Heaves but blow the beep test and get say 6.2 you wont get a score, you will just get a pass.

All the figures are above the pass mark, so basicaly its an incentive to not just pass but to get a 5 or 1 at least.



Cheers guys.


Troy :cheers:

Hey Troy,

If someone does a 1000 sit ups and a few push ups (but above the pass mark) how do you want to calculate this? Unless you want a 1,2,3,4,5 grade in each activity, then an aggregate mark based on each activity grade to give a final mark. Its all do able.

[Dan_]

I have a book that explains all this crap if you want it for a while. Its a Uni text i needed for my 1st year.

[hsvredsled]

I have a book that explains all this crap if you want it for a while. Its a Uni text i needed for my 1st year.

The good thing about XL is that someone always knows more or an easier way. I know only the tip of the iceberg, but I get by. When I really need help, I call in the big guns who are only a phonecall away.

[Dan_]

The good thing about XL is that someone always knows more or an easier way. I know only the tip of the iceberg, but I get by. When I really need help, I call in the big guns who are only a phonecall away.

This is true. I know nothing. The test was open book so i just followed the destructions. I use the pre formatted exel spreadsheets for my income and thats all. I dont need any of the other stuff

[BOMI]

Hey Troy,

If someone does a 1000 sit ups and a few push ups (but above the pass mark) how do you want to calculate this? Unless you want a 1,2,3,4,5 grade in each activity, then an aggregate mark based on each activity grade to give a final mark. Its all do able.




P/U's ..... S/U's ....... Heaves ... Beep test ..... Score
20 .......... 80 .......... 12 ........... 8.5 ............... 1
25 ........... 90 .......... 14 .......... 10.2 .............. 2
35 ........... 95 .......... 18 .......... 11.5 .............. 3
45 .......... 100 .......... 20 .......... 12 .............. 4
60 .......... 120 .......... 25 .......... 12.8 ................ 5




Anyone that gets above these scores will get the 1 2 3 4 or 5 to the right


I have a book that explains all this crap if you want it for a while. Its a Uni text i needed for my 1st year.

Thanks mate, im ok for now :cheers:


Troy :)

[Buffy]

Sorry to hijack this thread, but can you tell me Troy what level is expected from the beep test?
Just curious!! Is the distance 30mtrs or 40 mtrs?

Thanks :coffee:

[BOMI]

Sorry to hijack this thread, but can you tell me Troy what level is expected from the beep test?
Just curious!! Is the distance 30mtrs or 40 mtrs?

Thanks :coffee:

40 :eek: :eek:

This one is done over 20m, never heard of any higher then 25m

There are 5 or 6 different levels, the entry standard is 7.5 I think and thats piss easy!!

10.5 is the highest for what I am talking about at the moment.


Let me know if you need any other help with PT stuff Buffy :yep:


Troy :)

[Buffy]

20!!!! No wonder i did so badly last year. i did one with a training group I joined last year, trying to get back into shape, I managed to get to 6, and i swear it was 30 m or more. It was ridiculously hard.
I did query the distance with them :dunno: because i thought it looked longer. I am fast and I thought I should've had a better score.
BUT I have sinced left that group and joined another outdoor fitness group, lost 15kgs this year so far.In much better shape, haven't done another beep test so that would be interesting to see how i would go.

Thanks

[BOMI]

20!!!! No wonder i did so badly last year. i did one with a training group I joined last year, trying to get back into shape, I managed to get to 6, and i swear it was 30 m or more. It was ridiculously hard.
I did query the distance with them :dunno: because i thought it looked longer. I am fast and I thought I should've had a better score.
BUT I have sinced left that group and joined another outdoor fitness group, lost 15kgs this year so far.In much better shape, haven't done another beep test so that would be interesting to see how i would go.

Thanks


Yeah that would suck, even 5m more can make the test heaps harder!

Well done on the training and your results!! :yep:


Troy :)