If Cell Not Blank, Calculate & Round Off Result

    I would like to be able to round off the decimals resulted in the POV field on my worksheet and have a running total of the entries listed below. This occurs sometimes when the mileage units have decimals. In this example, the POV values display $1.00, 99 cents, and 93 cents. Their resulting total should be 2.92, but Excel calculates them as $2.93 due to the additional decimals.


    The problem is that if I apply an =ROUND function to the formula in the POV field, while it will display a correct answer if all the date and mileage fields are filled in for which the formula has been applied, it will not give a running total in instances where there are empty date/mileage fields, reporting "VALUE!". Is there a way to force Excel to display the running total?


    I have attached the worksheet, with the example above entered in. I have included the formula I've been trying to fix on my worksheet on the bottom as well.

    Re: Function To Round Off Decimals With Running Total


    Try using this formula... Place this in E6 and copy across


    =IF(ISBLANK(E5),"",FLOOR(E5*E3,0.01))


    HTH


    Craig

    Re: Function To Round Off Decimals With Running Total


    That fixes the rounding aspect of it for sure, but if I copy that formula to all the POV fields -- the date/mileage fields that are not yet filled in report #VALUE! in the corresponding POV rows and also the same in the total field. This isn't such a big deal for the POV field, but I need the total field to keep a running total as the user enters in each date / mileage amount. Thanks for all of your help though at figuring this out.

    Re: Function To Round Off Decimals With Running Total


    place these in the corresponding cells...


    E6 = =IF(OR(ISBLANK(E5),ISBLANK(E3),ISBLANK(E2)),"",FLOOR(E5*E3,0.01))
    E12 = =IF(OR(ISBLANK(E11),ISBLANK(E9),ISBLANK(E8)),"",FLOOR(E11*E9,0.01))
    E18 = =IF(OR(ISBLANK(E17),ISBLANK(E15),ISBLANK(E14)),"",FLOOR(E17*E15,0.01))


    I don't #VALUE... Try explaining what you do to get #VALUE so we can narrow the error down...

    Re: Function To Round Off Decimals With Running Total


    I would use ROUND instead of FLOOR. In E6 and across,


    [COLOR="Blue"]=IF(E5 = "", "", ROUND(E5 * E3, 2) )[/COLOR]


    That will not give a #VALUE! result.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

    Re: Function To Round Off Decimals With Running Total


    You will likely be better off in the longterm using a more efficient database style structure.


    See attached.
    The Date/$Rate table has been placed on a separate sheet and a dynamic named range created for this table. This table is referenced in the VLOOKUP for the applicable rate.


    A dynamic named range is also created for the POV column and this name is used in the SUM formula to keep a running total. It will update as new entries are made.


    The advantage of the database structure is that later, should you need them, you can easily add more fields (columns) such as for a name, department, etc. With this style of structure you generate Pivot Tables/Reports/Charts much easier than you current layout.


    Have also revised the formula per shg's suggestion.

    Re: Function To Round Off Decimals With Running Total


    Thanks Craig. That actually worked perfectly! Not sure what I was doing wrong before, but your formulas worked fine.


    I do like the idea of doing things more efficiently though and being able to add rows more easily later, so I'll see if I can build upon your idea AAE. Thanks!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login