ROUNDUP

NOCHOKE · May 8th, 2002, 05:15 PM

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 · May 8th, 2002, 05:20 PM

Quote:

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. · May 8th, 2002, 05:21 PM

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

NOCHOKE · May 8th, 2002, 05:23 PM

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

NOCHOKE · May 8th, 2002, 05:45 PM

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 · May 8th, 2002, 05:54 PM

Quote:

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. · May 8th, 2002, 06:16 PM

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 · May 8th, 2002, 08:37 PM

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 · May 8th, 2002, 08:53 PM

Quote:

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 · May 9th, 2002, 12:12 AM

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)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 8th, 2002, 05:15 PM	#1
NOCHOKE New Member Join Date: May 2002 Posts: 4	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?

May 8th, 2002, 05:21 PM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	=CEILING((A1-36)*80%,1)

May 8th, 2002, 05:23 PM	#4
NOCHOKE New Member Join Date: May 2002 Posts: 4	It worked on my test page. I'll give it a shot on the big sheet. Thank you very much sir.

May 8th, 2002, 05:45 PM	#5
NOCHOKE New Member Join Date: May 2002 Posts: 4	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 ]

May 8th, 2002, 06:16 PM	#7
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	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 ]

May 8th, 2002, 08:37 PM	#8
NOCHOKE New Member Join Date: May 2002 Posts: 4	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?

May 9th, 2002, 12:12 AM	#10
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	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)