Formula for rounding and number management not working

Hi and welcome to the forum

1st thing I need to ask is - are you working with real time, or just numbers? Real excel time is a decimal of 1 (day), such that 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 06:00 PM) is 0.75

Can you upload a sample of what you have and what you want?

Originally Posted by dominatro

I have this formula:
ROUND(L19*(IF(C19<=2.083, ROUNDDOWN(C19,0), IF(C19<=2.5,C19=2.5, ROUNDUP(C19,0)),2)

In it i am trying to get a value based on hours. I want any hourly time which results in 0.083 of an hour or less to be rounded to the nearest whole number, any number which falls between 0.084 and 0.583 to be rounded to the nearest .5 and anything above 0.583 to be rounded up to the nearest whole number.

First, it appears that C19 is time in decimal hours. It is unclear whether C19 has been rounded explicitly to 3 decimal places. If not, as I assume, it is improper for you to use decimal fractions like 0.083 (really 5/60) and 0.583 (really 35/60).

Second, it is unclear why you use 2.xxx in your formula, if you simply want to ROUNDDOWN or ROUNDUP based on fractional hours. I will rely on your verbal description.

Ostensibly, we would like to write:

=ROUND(L19*(INT(C19)+IF(MOD(C19,1)>35/60,1,IF(MOD(C19,1)>5/60,0.5))),2)

MOD(C19,1) returns the fraction of an hour. INT(C19) is the same as ROUNDDOWN(C19,0). Adding 1 is the same as ROUNDUP(C19,0).

However, if L19 is 100 and C19 is =12+5/60, for example, that formula results in 1250 instead of 1200 as intended. (Obviously, 5/60 is not >5/60.)

That is due to internal (binary floating-point) arithmetic anomalies. I could explain further, if you wish.

The following formula is more reliable:

=ROUND(L19*(INT(C19)+IF(C19>INT(C19)+35/60,1,IF(C19>INT(C19)+5/60,0.5))),2)

@FDibbins it is the fraction of an hour not a day and not real time.

Originally Posted by joeu2004

First, it appears that C19 is time in decimal hours. It is unclear whether C19 has been rounded explicitly to 3 decimal places. If not, as I assume, it is improper for you to use decimal fractions like 0.083 (really 5/60) and 0.583 (really 35/60).

It is rounded to 2 but I could change it to three, although fractions is probably better.

Second, it is unclear why you use 2.xxx in your formula, if you simply want to ROUNDDOWN or ROUNDUP based on fractional hours. I will rely on your verbal description.

The 2 part is unimportant. This excel sheet is for invoice purposes. We offer 5 minute grace periods on our charging rate and we charge by the half hour. So, for example, if we work for 3 hours and 4 minutes we want to only charge for 3 hours. If we work for 3 hours and 10 minutes we want to charge for 3.5 hours and if we work for 3 hours and 38 minutes we want to charge for 4 hours.

Ostensibly, we would like to write:

=ROUND(L19*(INT(C19)+IF(MOD(C19,1)>35/60,1,IF(MOD(C19,1)>5/60,0.5))),2)

MOD(C19,1) returns the fraction of an hour. INT(C19) is the same as ROUNDDOWN(C19,0). Adding 1 is the same as ROUNDUP(C19,0).

However, if L19 is 100 and C19 is =12+5/60, for example, that formula results in 1250 instead of 1200 as intended. (Obviously, 5/60 is not >5/60.)

That is due to internal (binary floating-point) arithmetic anomalies. I could explain further, if you wish.

The following formula is more reliable:

=ROUND(L19*(INT(C19)+IF(C19>INT(C19)+35/60,1,IF(C19>INT(C19)+5/60,0.5))),2)

It looks like this formula could work! Thanks!

Yup it works perfectly! Thank you!

One more question, my C19 cell is currently formatted as a number but it will be easier to enter in formulas of simple math, such as "=4 + (19/60)" is it best to keep the number format or convert it to general? Number at least confines the number to a specified number of decimal places so it doesn't look messy, but still calculates the real value right?

Originally Posted by joeu2004

First, it appears that C19 is time in decimal hours. It is unclear whether C19 has been rounded explicitly to 3 decimal places. If not, as I assume, it is improper for you to use decimal fractions like 0.083 (really 5/60) and 0.583 (really 35/60).

Originally Posted by dominatro

It is rounded to 2 but I could change it to three, although fractions is probably better.

Then why are you comparing with values with 3 decimal places?

I suspect there is a misunderstanding.

When I wrote "rounded explicitly", I meant: using the ROUND function. Using your later example, I mean =ROUND(4 + 19/60,2) instead of simply =4 + 19/60.

I suspect you are referring to formatting as Number (or something) with 2 decimal places.

Usually [1], formatting alone affects only the appearance of the value. It does not change the actual value. For example, =4 + 19/60 might appear as 4.32 when formatted with 2 decimal places; but its actual value is about 4.31666666666667 (and not even that exactly). So IF(C19=4.32,TRUE) returns FALSE, despite appearances.

[1] Re: formatting does not change the actual value. Unless you set the "Precision as displayed" option, which I do not recommend.

Originally Posted by joeu2004

The following formula is more reliable:
=ROUND(L19*(INT(C19)+IF(C19>INT(C19)+35/60,1,IF(C19>INT(C19)+5/60,0.5))),2)

Originally Posted by dominatro

Yup it works perfectly!

If you explicitly round C19 using the ROUND function, it would behoove you to explicitly round comparative values as well.

Although the formula above might work by coincidence (because 5/60 and 35/60 are greater than their rounded counterparts), in general the following is more reliable:

=ROUND(L19*(INT(C19)+IF(C19>ROUND(INT(C19)+35/60,2),1,
IF(C19>ROUND(INT(C19)+5/60,2),0.5))),2)

Change ROUND(...,2) to ROUND(...,3) if you change C19 and round explicitly to 3 decimal places.

Originally Posted by dominatro

One more question, my C19 cell is currently formatted as a number but it will be easier to enter in formulas of simple math, such as "=4 + (19/60)" is it best to keep the number format or convert it to general? Number at least confines the number to a specified number of decimal places so it doesn't look messy, but still calculates the real value right?

As noted above, how you format the value does not change it. So whether you format as General or as Number with 2 or 3 decimal places is a quality issue. It is up to you entirely.

Usually, I prefer not to format as General, especially for public documents like invoices, since we have no control over the appearance of the result.

Originally Posted by dominatro

nevermind i found a fraction option

I presume you refer to a Fraction format like the Custom format "0 ??/60" without quotes.

Again, keep in mind that the Fraction format only affects appearance, not the actual value.

Testing for more than x 5/60 and x 35/60 probably works.

But beware of testing for equal to x 5/60 or x 35/60.

Usually, the result of arithmetic will not be identically equal, despite appearances. Unless, again, you take steps to explicitly round the arithmetic results appropriately. For example,
--TEXT(C19+C20,"0 ??/60").