Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Jaco Jacobs
 
Posts: n/a
Default Rounding numbers up or down

I have a spreadsheet with two worksheets. The one worksheet contants a load
of data and I want the other to get the information and then round the number
to the next highest as soon as it gets to .6 All the other values I would
like it to keep.

The current formula to get the data from the "Totals" worksheet is
=SUMIF(Totals!A:A,B7,Totals!B:B)

I would like it to take the number that it gets and if it is .6 (OF ANY
NUMBER) i want it to round up to the next highest number. If the number is
between 0 and .5 i want it to show that single decimal value.

Hope this makes sense.
  #2   Report Post  
Posted to microsoft.public.excel.misc
JMB
 
Posts: n/a
Default Rounding numbers up or down

If the number you want to round is in cell A1 (for your purposes replace A1
with your SUMIF statement), try
=ROUNDUP(A1,MOD(ROUND(A1,2),1)<0.6)

I used ROUND(A1,2) because Excel can have issues w/binary fractions (some
decimals cannot be stored accurately in binary- similar to 1/3 cannot be
represented accurately as a decimal). You may want to change the level of
precision depending on your data.


"Jaco Jacobs" wrote:

I have a spreadsheet with two worksheets. The one worksheet contants a load
of data and I want the other to get the information and then round the number
to the next highest as soon as it gets to .6 All the other values I would
like it to keep.

The current formula to get the data from the "Totals" worksheet is
=SUMIF(Totals!A:A,B7,Totals!B:B)

I would like it to take the number that it gets and if it is .6 (OF ANY
NUMBER) i want it to round up to the next highest number. If the number is
between 0 and .5 i want it to show that single decimal value.

Hope this makes sense.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Rounding numbers up or down boyshanks Excel Discussion (Misc queries) 13 April 8th 09 04:03 PM
how to loose all numbers after decimal (not rounding) arjay9 Excel Worksheet Functions 3 January 13th 06 02:00 AM
Rounding numbers to the nearest 5 or 0 Fieldmedic Excel Worksheet Functions 3 July 17th 05 07:51 AM
Match Last Occurrence of two numbers and Return Date Sam via OfficeKB.com Excel Worksheet Functions 6 April 5th 05 01:40 PM
How do I make Excel stop rounding off my numbers that are 16 digi. Aida Excel Discussion (Misc queries) 1 December 6th 04 05:34 PM


All times are GMT +1. The time now is 12:56 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"