Help With Excel Formulas

[hlewton]

Hans
How do I add the ROUND function to this formula - =IFERROR(AVERAGE(B3:B39),"") - and make it round to 0 decimal places?

I have tried
=IFERROR(ROUND(AVERAGE(B3:B39),""),0) and
=IFERROR(ROUND(AVERAGE(B3:B39),"", 0)) and I am not making it work.

[StuartR]

=IFERROR(ROUND(AVERAGE(B3:B39),0),"")

[hlewton]

=IFERROR(ROUND(AVERAGE(B3:B39),0),"")

Thanks that worked great. Not sure why I couldn't figure out.

[hlewton]

=IF(B4="","",(B4-A4)) - this formula works for me. So I tried using it as an example to create a new formula in a different spreadsheet, because in both formulas the cells I want to be blank are based on a numeric value. I came up with this formula =IF(SUM(C3:M3) "","",(SUM(C3:M3))). This does not work and gives an error.

I replaced the first "" with <=0 and this formula =IF(SUM(C3:M3)<=0,"",(SUM(C3:M3)))
works for me.

Why didn’t =IF(SUM(C3:M3) "","",(SUM(C3:M3))) formula work?

Also using the same formula directly above, when the cell has a value and isn’t blank it displays a green triangle in its upper left corner. Please see attachment. I know how to get rid of it but what I do not understand is why is it there in the first place. The range I want to sum is in columns C through M and this formula is in column P. The error says the Formula Omits Adjacent Cells, which I am not interested in. I believe my formula clearly says I am not interested in columns N and O so why the green triangle?

Also, again using the same formula above, I copied it to about 50 rows and they all displayed the green triangle. I highlighted them, and chose the option inside the attachment to ignore the error. All the green triangles were cleared. However, when a new numeric value appears in column P in any of the cells the green triangle reappears in that cell. Why is that happening, and can it be avoided?

Thanks.

Green Triangle.jpg

[HansV]

The condition in the original formula is

B4=""

In your new formula, you used

SUM(C3:M3) ""

There is no =, that's why it doesn't work. It should be

SUM(C3:M3)=""

The green triangles are just suggestions. They are not always relevant. You can turn them off permanently bt clicking Error Checking Options... in the drop-down, or by selecting File > Options > Formulas.

S0641.png

[hlewton]

Thank you for the answers.

[hlewton]

The condition in the original formula is

B4=""

In your new formula, you used

SUM(C3:M3) ""

There is no =, that's why it doesn't work. It should be

SUM(C3:M3)=""

The green triangles are just suggestions. They are not always relevant. You can turn them off permanently bt clicking Error Checking Options... in the drop-down, or by selecting File > Options > Formulas.


S0641.png

If I am understanding you correctly, I edited my formula to read = IF(SUM(C3:M3)= "","",(SUM(C3:M3))). Not sure why but this formula places a 0 in the cells that have no decimal value in them yet. The double quotes should have made it display a blank cell, I believe. I placed this formula in the cell you can see in the attachment below that displays a 0. I opened the information from the green triangle so you can see that for this cell. As you can see the cell above the one displaying 0 is blank, as I want it to be. The formula in it is the one I mentioned before, =IF(SUM(C18:M18)<=0,"",(SUM(C18:M18))). I would like to try to stay consistent with the formulas but = IF(SUM(C3:M3)= "","",(SUM(C3:M3))) doesn't seem to work for me. Am I still not typing it correctly?

Green Triangle1.jpg

[HansV]

Sorry, a sum is actually never "". If the cells are blank, their sum is 0.

=IF(SUM(C3:M3)=0,"",SUM(C3:M3))

Keep in mind that this only checks whether the sum is 0. For example, if C3 = 20, D3 = -20, and E3 to M3 are all empty, the sum will be 0 too.

[hlewton]

Thanks, now I understand it.

[hlewton]

=IF(AVERAGE($B$3:$B$400)=0,"",AVERAGE($B$3:$B$400)&" is Average Days Between Changing Batteries")

In the above formula, it displays the number of day with a decimal point of 14 digits. How do I format it so it only displays to 2 decimal places?

[Leif]

x.jpg

[hlewton]

x.jpg

I tried that before posting. It doesn't work when the number is combined with the words after the ampersand sign. At least it didn't work for me. Maybe I did something wrong but I don't know what it would have been.

Thanks

[hlewton]

I got it working using this formula,
=IF(AVERAGE($B$3:$B$400)=0,"",TEXT(AVERAGE($B$3:$B$400),"0.2")&" is Average Days Between Changing Batteries")

BUT I was wondering if there were a way to format the number without using the TEXT function in the formula.

[HansV]

You could do the following:

1) Change the formula to

=AVERAGE($B$3:$B$400)

2) In the Format Cells dialog, select Custom in the Category list, then enter

0.00" is Average Days Between Changing Batteries";;

in the Type box. Click OK.

S0707.png

[hlewton]

Thank you. It worked great.

[StuartR]

Another solution would be to use the ROUND function

=IF(AVERAGE($B$3:$B$400)=0,"",ROUND(AVERAGE($B$3:$B$400),2)&" is Average Days Between Changing Batteries")

[hlewton]

Another solution would be to use the ROUND function

=IF(AVERAGE($B$3:$B$400)=0,"",ROUND(AVERAGE($B$3:$B$400),2)&" is Average Days Between Changing Batteries")

Thank you. I will try that also. I am trying to keep examples of the formulas I have asked for so, hopefully, I can make some of them work for me without having to ask for help.

[HansV]

Hi Stuart,

There is a slight difference. TEXT would display 1.2034 as "1.20 is Average Days Between Changing Batteries", while ROUND would display it as "1.2 is Average Days Between Changing Batteries".
The one to use depends on one's preferences.

[hlewton]

2) In the Format Cells dialog, select Custom in the Category list, then enter

0.00" is Average Days Between Changing Batteries";;

in the Type box. Click OK.


S0707.png

What does the ";;" do at the end of this format?

[HansV]

A custom number format can contain up to four sections, separated by semi-colons.
The first part is for positive numbers.
The second part is for negative numbers.
The third part is for zero values.
The fourth part is for text values.

For example:

0.00;-0;"Zero"

means that positive numbers will be displayed with 2 decimal places, negative numbers with 0 decimal places and 0 as the text value "Zero".
The format

0.00" is Average Days Between Changing Batteries";;

specifies that positive numbers will be displayed with 2 decimal places followed by the fixed text " is Average Days Between Changing Batteries".
Since the sections for negative numbers and zero are empty (there is nothing between the semi-colons or after the second semi-colon), negative numbers and zeros will not be displayed.