Create a roundup for a figure when I am using a IF function for something else.

bluedusty

New Member
Joined
Jan 24, 2021
Messages
7
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
Hello,

I am wondering how I go about using the round formula when I am already using and IF function in a cell for another reason.

Currently I have this formula in place:
=IF($J$3="",SUM(D5:D28),IF($J$3="23 Hour Day",SUM(D5:D27),IF($J$3="24 Hour Day",SUM(D5:D28),IF($J$3="25 Hour Day",SUM(D5:D29))))) - I need to provide hourly information so have it set to add or remove a box depending on time stamp selected. D5:D28/9 will normally be a long figure for example 0.299583333333

What I need to do is return the value in cells D5:D28/29 (Depending on IF function) to 3 decimal places. Cells D5:D28 display 2 decimal place but could make that 3 if it helps.

When I try to add the round function in it just seems to return a true, false or spill comment not an acutal figure.

Hopefully I have made what I am after clear enough, All help much appreciated.

Thanks
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=ROUND(IF($J$3="",SUM(D5:D28),IF($J$3="23 Hour Day",SUM(D5:D27),IF($J$3="24 Hour Day",SUM(D5:D28),IF($J$3="25 Hour Day",SUM(D5:D29))))),3)
 
Solution

bluedusty

New Member
Joined
Jan 24, 2021
Messages
7
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
Hi Fluff,

This seems to accept the formula but if I put figures into cells D5:D29 it does not change from 0.00

1612389049471.png


the 0.14 is actually .1354166666667 but the sum of rates where this formula is doen not change.

Thanks for your help so far, very much appreciated
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
Are you sure that D5:D29 are numbers & not text?
Also what is in J3?
 

bluedusty

New Member
Joined
Jan 24, 2021
Messages
7
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows

ADVERTISEMENT

D5:D29 are numbers as per the box above showing the reading of 0.14

J3 is a list that you either pick 23hr day, 24 hr day or 25hr day.

If j3 is selected to 24hr day it will only look at figures in D5:28 and if j3 is on 23hr day it only looks at D5:D27 etc.

Thanks
 

bluedusty

New Member
Joined
Jan 24, 2021
Messages
7
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
Sorry Fluff, Thank you very much, that formula did work - Operator error doh
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,373
Messages
5,635,884
Members
416,886
Latest member
coreyalaurence37

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top