Hi

Mental block. Using Excel to cost out a project. One example, weed
contrail fabric. Spreadsheet works out that I need 1.25 rolls at £7.99, so
cost is £9.99 (rounded up).

In reality I'd have to buy 2 rolls, so cost would be £15.98.

I can format the cell to 'no decimal places', which in this case shows 1
roll & still £9.99.

How do I get Excel to work in whole numbers, rounded up?


--
Dave - The Medway Handyman
www.medwayhandyman.co.uk

The Medway Handyman wrote:
Hi

Mental block. Using Excel to cost out a project. One example, weed
contrail fabric. Spreadsheet works out that I need 1.25 rolls at £7.99, so
cost is £9.99 (rounded up).

In reality I'd have to buy 2 rolls, so cost would be £15.98.

I can format the cell to 'no decimal places', which in this case shows 1
roll & still £9.99.

How do I get Excel to work in whole numbers, rounded up?

use the CEILING() function.

ceiling(a1,1)

The Medway Handyman wrote:
Hi

Mental block. Using Excel to cost out a project. One example, weed
contrail fabric.

Fell off the back of a 'plane did it ;-)

Spreadsheet works out that I need 1.25 rolls at £7.99, so
cost is £9.99 (rounded up).

In reality I'd have to buy 2 rolls, so cost would be £15.98.

I can format the cell to 'no decimal places', which in this case shows 1
roll & still £9.99.

How do I get Excel to work in whole numbers, rounded up?

Use the =roundup(number, decimal_places) function.

e.g.

A1 contains 1.5
A2 contains =roundup(a1,0)

--
Cheers,

John.

/================================================== ===============\
| Internode Ltd - http://www.internode.co.uk |
|-----------------------------------------------------------------|
| John Rumm - john(at)internode(dot)co(dot)uk |
\================================================= ================/

Mental block. Using Excel to cost out a project. One example, weed
contrail fabric. Spreadsheet works out that I need 1.25 rolls at £7.99,
so cost is £9.99 (rounded up).

In reality I'd have to buy 2 rolls, so cost would be £15.98.

I can format the cell to 'no decimal places', which in this case shows 1
roll & still £9.99.

How do I get Excel to work in whole numbers, rounded up?

=(B2*IF(INT(A2)=A2,A2,INT(A2)+1))

Where cell 'B2' is the cost, and cell 'A2' is the quantity

"John Rumm" wrote in message
et...
The Medway Handyman wrote:
Hi

Mental block. Using Excel to cost out a project. One example, weed
contrail fabric.

Fell off the back of a 'plane did it ;-)

Spreadsheet works out that I need 1.25 rolls at £7.99, so cost is £9.99
(rounded up).

In reality I'd have to buy 2 rolls, so cost would be £15.98.

I can format the cell to 'no decimal places', which in this case shows 1
roll & still £9.99.

How do I get Excel to work in whole numbers, rounded up?

Use the =roundup(number, decimal_places) function.

e.g.

A1 contains 1.5
A2 contains =roundup(a1,0)


If you're doing that, just spray the roundup on the weeds

I'll fetch my coat.
:-)

The Medway Handyman wrote:
Hi

Mental block. Using Excel to cost out a project. One example, weed
contrail fabric. Spreadsheet works out that I need 1.25 rolls at £7.99, so
cost is £9.99 (rounded up).

In reality I'd have to buy 2 rolls, so cost would be £15.98.

I can format the cell to 'no decimal places', which in this case shows 1
roll & still £9.99.

How do I get Excel to work in whole numbers, rounded up?


add one half and round down.

"The Natural Philosopher" wrote in message
...
The Medway Handyman wrote:
Hi

Mental block. Using Excel to cost out a project. One example, weed
contrail fabric. Spreadsheet works out that I need 1.25 rolls at £7.99,
so cost is £9.99 (rounded up).

In reality I'd have to buy 2 rolls, so cost would be £15.98.

I can format the cell to 'no decimal places', which in this case shows 1
roll & still £9.99.

How do I get Excel to work in whole numbers, rounded up?


add one half and round down.

err, no, 1.25 + 0.5 is 1.75. Round down is still 1, he wants 2. Add 0.5 and
round (up or down) is what you meant.

He should add 0.5 and round to nearest integer (not sure possible in excel)
or, easier, add 0.99 and truncate.

In Excel, number in A1 (say) - B(1) is set to "=INT(A1+0.99)" then use B1


--
Bob Mannix
(anti-spam is as easy as 1-2-3 - not)

"Bob Mannix" wrote in message
...
"The Natural Philosopher" wrote in message
...
The Medway Handyman wrote:
Hi

Mental block. Using Excel to cost out a project. One example, weed
contrail fabric. Spreadsheet works out that I need 1.25 rolls at £7.99,
so cost is £9.99 (rounded up).

In reality I'd have to buy 2 rolls, so cost would be £15.98.

I can format the cell to 'no decimal places', which in this case shows 1
roll & still £9.99.

How do I get Excel to work in whole numbers, rounded up?


add one half and round down.

err, no, 1.25 + 0.5 is 1.75. Round down is still 1, he wants 2. Add 0.5
and round (up or down) is what you meant.

He should add 0.5 and round to nearest integer (not sure possible in
excel) or, easier, add 0.99 and truncate.

In Excel, number in A1 (say) - B(1) is set to "=INT(A1+0.99)" then use B1


--
Bob Mannix
(anti-spam is as easy as 1-2-3 - not)


John Rumm has given the answer, short and easy.


Use the =roundup(number, decimal_places) function.

e.g.

A1 contains 1.5
A2 contains =roundup(a1,0)

John Rumm wrote:
The Medway Handyman wrote:
Hi

Mental block. Using Excel to cost out a project. One example, weed
contrail fabric.

Fell off the back of a 'plane did it ;-)

Spreadsheet works out that I need 1.25 rolls at £7.99, so cost is
£9.99 (rounded up).

In reality I'd have to buy 2 rolls, so cost would be £15.98.

I can format the cell to 'no decimal places', which in this case shows
1 roll & still £9.99.

How do I get Excel to work in whole numbers, rounded up?

Use the =roundup(number, decimal_places) function.

e.g.

A1 contains 1.5
A2 contains =roundup(a1,0)


Yes, that actually changes the value rather than just formatting it.
The ceiling function is a new one on me, but I guess it does the same thing

"Slider" wrote in message
...

"Bob Mannix" wrote in message
...
"The Natural Philosopher" wrote in message
...
The Medway Handyman wrote:
Hi

Mental block. Using Excel to cost out a project. One example, weed
contrail fabric. Spreadsheet works out that I need 1.25 rolls at
£7.99, so cost is £9.99 (rounded up).

In reality I'd have to buy 2 rolls, so cost would be £15.98.

I can format the cell to 'no decimal places', which in this case shows
1 roll & still £9.99.

How do I get Excel to work in whole numbers, rounded up?


add one half and round down.

err, no, 1.25 + 0.5 is 1.75. Round down is still 1, he wants 2. Add 0.5
and round (up or down) is what you meant.

He should add 0.5 and round to nearest integer (not sure possible in
excel) or, easier, add 0.99 and truncate.

In Excel, number in A1 (say) - B(1) is set to "=INT(A1+0.99)" then use
B1


--
Bob Mannix
(anti-spam is as easy as 1-2-3 - not)


John Rumm has given the answer, short and easy.


Use the =roundup(number, decimal_places) function.

e.g.

A1 contains 1.5
A2 contains =roundup(a1,0)


Ah yes, same effect, same number of cells but somewhat more elegant!

I stand corrected.


--
Bob Mannix
(anti-spam is as easy as 1-2-3 - not

The Natural Philosopher wrote:
The Medway Handyman wrote:
Hi

Mental block. Using Excel to cost out a project. One example, weed
contrail fabric. Spreadsheet works out that I need 1.25 rolls at
£7.99, so cost is £9.99 (rounded up).

In reality I'd have to buy 2 rolls, so cost would be £15.98.

I can format the cell to 'no decimal places', which in this case shows
1 roll & still £9.99.

How do I get Excel to work in whole numbers, rounded up?


add one half and round down.

That is the way to round off, rather than up (as in add 0.5 and truncate
to an integer)


--
Cheers,

John.

/================================================== ===============\
| Internode Ltd - http://www.internode.co.uk |
|-----------------------------------------------------------------|
| John Rumm - john(at)internode(dot)co(dot)uk |
\================================================= ================/