ROUNDUP

[NOCHOKE]

I am pretty much a novice at this, so if you can and would like to help, please keep your suggestions simple... You've heard of Napolean's Idiot, right? btw, I am using Office 97.

So, what I am trying to do is to get the result of a formula to roundup. I am the secretary for a golf league and want my spreadsheet to compute players' handicaps. Our league uses a simple 80% of the difference between par and your score (rounded up to the next whole number) as the handicap.

Say a player shoots a 39, and par is 36, the difference is 3. 80% of 3 is 2.4. The formula I use to get to the 2.4 is:

=(A1-36)*80%

A1 is the score the player shot. How do I get Excel to round this result up to the next whole number?

 

[Aladin Akyurek]

On 2002-05-08 16:15, NOCHOKE wrote:
I am pretty much a novice at this, so if you can and would like to help, please keep your suggestions simple... You've heard of Napolean's Idiot, right? btw, I am using Office 97.

So, what I am trying to do is to get the result of a formula to roundup. I am the secretary for a golf league and want my spreadsheet to compute players' handicaps. Our league uses a simple 80% of the difference between par and your score (rounded up to the next whole number) as the handicap.

Say a player shoots a 39, and par is 36, the difference is 3. 80% of 3 is 2.4. The formula I use to get to the 2.4 is:

=(A1-36)*80%

A1 is the score the player shot. How do I get Excel to round this result up to the next whole number?

=ROUNDUP((A1-36)*80%,0) ?

 

[Mark W.]

=CEILING((A1-36)*80%,1)

 

[NOCHOKE]

It worked on my test page. I'll give it a shot on the big sheet. Thank you very much sir.

 

[NOCHOKE]

Ok, lets take it another step or two farther... Say that in week 2, the player shoots a 43. Currently I am manually adding the two rounds together, subtracting par then multiplying the result by 80% and rounding up.

=ROUNDUP((A1-36)*80%,0) worked for week one.

Week two's score would be in B1 and par would now be 72 instead of 36. What would that formula be? And then week 3 (in C1), and week 4 (in D1)...

After week 4, we go to a handicap based on the same 80% as noted above, except that we use the last four rounds only. What would my formula be for the rest of the season?
This message was edited by NOCHOKE on 2002-05-08 16:46

 

[Aladin Akyurek]

On 2002-05-08 16:45, NOCHOKE wrote:
Ok, lets take it another step or two farther... Say that in week 2, the player shoots a 43. Currently I am manually adding the two rounds together, subtracting par then multiplying the result by 80% and rounding up.

=ROUNDUP((A1-36)*80%,0) worked for week one.

Week two's score would be in B1 and par would now be 72 instead of 36. What would that formula be? And then week 3 (in C1), and week 4 (in D1)...

After week 4, we go to a handicap based on the same 80% as noted above, except that we use the last four rounds only. What would my formula be for the rest of the season?
This message was edited by NOCHOKE on 2002-05-08 16:46

Just guessing and the guess involves the first part:

=ROUNDUP((SUM(A1:D1)-COUNT(A1:D1)*36)*80%,0)

Is that what you want?

 

[Mark W.]

Enter the formula...

=ROUNDUP((SUM(OFFSET(A1,,-MIN(3,COLUMN()-1),,MIN(4,COLUMN())))-MIN(4,COLUMN())*36)*80%,0)

...into cell A2 and fill right
This message was edited by Mark W. on 2002-05-08 17:17

 

[NOCHOKE]

Aladin, I couldn't get yours to work.

Mark, With yours I get the right result in A2, but it does not change when I add test figures in B1, C1, D1, etc. What am I missing?

 

[Yogi Anand]

On 2002-05-08 16:45, NOCHOKE wrote:
Ok, lets take it another step or two farther... Say that in week 2, the player shoots a 43. Currently I am manually adding the two rounds together, subtracting par then multiplying the result by 80% and rounding up.

=ROUNDUP((A1-36)*80%,0) worked for week one.

Week two's score would be in B1 and par would now be 72 instead of 36. What would that formula be? And then week 3 (in C1), and week 4 (in D1)...

After week 4, we go to a handicap based on the same 80% as noted above, except that we use the last four rounds only. What would my formula be for the rest of the season?
This message was edited by NOCHOKE on 2002-05-08 16:46

Hi NOCHOKE:
If I understood you correctly, try the following formula in a cell in column A and then replicate it to the right in columns B, C, and D

=ROUNDUP((SUM($A1:A1)-COLUMN()*36)*0.8,0)

If I did not understand you correctly the above formula may not work -- in that case my apoligies.

Regards!

 

[Aladin Akyurek]

On 2002-05-08 19:37, NOCHOKE wrote:
Aladin, I couldn't get yours to work.


Mine was not meant to be copied. If you want to have it each week, then:

In A2 enter and copy across to D2:

=ROUNDUP((SUM($A$1:A1)-COUNT($A$1:A1)*36)*80%,0)