Formula for rounding and number management not working

[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.

The formula above doesn't work as i get the error that there are too many arguments...i am assuming it has a problem with the if statement being multiplied against the L19.

Thanks for your help.

[dominatro]

ok i sort of figured this out:

ROUND((L19*(IF(C19<=2.083, ROUNDDOWN(C19,0), IF(C19<=2.583,C19=2.5, ROUNDUP(C19,0))))),2)

but i need to figure out how to only look at the decimal places and not the actual number to the left of the decimal, such that the formula would look something like this:

ROUND((L19*(IF(C19<=x.083, ROUNDDOWN(C19,0), IF(C19<=x.583,C19=x.5, ROUNDUP(C19,0))))),2)

Also i noticed the formula fails when the value is equal to 2.5 (it seems to give a value of 1), and anything below 2.083 or between 2.5 and 2.583 gives nothing but a "-".

[FDibbins]

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?

[joeu2004]

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)

[dominatro]

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

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?

[dominatro]

nevermind i found a fraction option

[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.

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.

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)

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.

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.

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").

[dominatro]

I'm not sure i completely follow, but the originally proposed formula works exactly as intended in all tested cases where C19 is less than x + 5/60, C19 is equal to x + 5/60, and C19 is greater than x + 5/60, furthermore it works perfectly the same for the sane tests with x + 35/60.

So thanks for your help with that.

Also i understand now the difference between what is being calculated and what is being shown. I also noticed that the invoice i had had a data valuation which i modified to allow for decimal numbers. (it was giving me an alert everytime i put a number other than an integer into the cell)