Sum of Cells w/DOLLARDE Function Not Adding Correctly

[Disarmonious]

Hello,

Cell ZZ50 has this formula, =SUM(M5:M39,AQ5:AQ39). All of these cells has a value of 1.18 each, but cell ZZ50 returns the total as being 82.83. The correct calculation should be 82.60. ALL cells M5:M39 - AQ5:AQ39 are using the DOLLARDE formula, and each cell has the Number format. Cell ZZ50 is also using a number format. Can anyone help me figure out what's going on with the calculation on this cell?

 

[Eric W]

DOLLARDE returns a number, but it's not rounded. For example,

=DOLLARDE(1.03,16)

converts 1 3/16 to 1.1875. If you have a number format that only shows 2 decimals, you'll only see 2 decimals, but the remaining digits are still there. This is why your total is higher than you expect. If you want the numbers to be the same as displayed, try something like:

=ROUNDDOWN(DOLLARDE(1.03,16),2)

 

[Disarmonious]

Hello Eric,

So I tried;

=ROUNDDOWN(DOLLARDE(E5,1),2) and I get 111.00. I even used your example;
=ROUNDDOWN(DOLLARDE(E5,16),2) and got the same return value, 111.00. since 1:11 is entered in E5, M5 should return with a value of 1.18.

I'll also add that cells E5:E29 - cells AQ5:AQ29 all are "time" values with the custom format of 00\:00. So essentially I'm using the M and Q columns to convert these cells into decimal time, and then that ZZ cell to add all the decimal times. I apologize that I didn't mention this from the original post if this changes anything.[

QUOTE=Eric W;5077444]DOLLARDE returns a number, but it's not rounded. For example,

=DOLLARDE(1.03,16)

converts 1 3/16 to 1.1875. If you have a number format that only shows 2 decimals, you'll only see 2 decimals, but the remaining digits are still there. This is why your total is higher than you expect. If you want the numbers to be the same as displayed, try something like:

=ROUNDDOWN(DOLLARDE(1.03,16),2)[/QUOTE]

 

[Eric W]

Well, you've got some interesting things going on.

If I understand correctly, you have E5 formatted with 00\:00. So if you enter 118, it displays as 01:18. Then you want to take that "time" and convert it to decimal so that you can sum the times.

The problem is that the number in E5 really is 118, not 1:18, not 1.18. The fact that the custom display shows it as 1:18 doesn't change the actual value that you entered. DOLLARDE works on decimals, and E5 doesn't have any. Try this:

=DOLLARDE(E5/100,60)

That should convert your pseudo-time to a decimal time. I don't know if rounding will still play a role for you.

 

[Disarmonious]

Hello,

So cells (M5:M39, AQ5:AQ39) all have that formula; =DOLLARDE(M5/100,60). I do get the 1.18 if the user types “111” (01:11) in cell M5 (that has the custom format ‘00\:00’). The issue I’m running into is cell ZZ50 has the function; =SUM(M5:M29,AQ5:AQ29), and I’m not getting an accurate result, “82.83” instead of 82.60. I think you were on to something on your first reply, with the numbers not rounding right, but I only want the cells to show two decimals, and I want an accurate total “82.60". Hopefully there's a solution to this madness...

 

[Disarmonious]

Eric,

I got this figured out. Thank you for your patience, time, and most importantly, help. Have a good day my friend!

 

[Eric W]

Glad you got it sorted.