## Help With Excel Formulas hlewton
PlatinumLounger
Posts: 3542
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

### Re: Help With Excel Formulas

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.
Regards,
hlewton StuartR
Posts: 12237
Joined: 16 Jan 2010, 15:49
Location: London, Europe

### Re: Help With Excel Formulas

=IFERROR(ROUND(AVERAGE(B3:B39),0),"")
StuartR hlewton
PlatinumLounger
Posts: 3542
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

### Re: Help With Excel Formulas

StuartR wrote:
27 Jul 2021, 13:09
=IFERROR(ROUND(AVERAGE(B3:B39),0),"")
Thanks that worked great. Not sure why I couldn't figure out.
Regards,
hlewton hlewton
PlatinumLounger
Posts: 3542
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

### Re: Help With Excel Formulas

=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
You do not have the required permissions to view the files attached to this post.
Regards,
hlewton HansV
Posts: 76156
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Help With Excel Formulas

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
You do not have the required permissions to view the files attached to this post.
Regards,
Hans hlewton
PlatinumLounger
Posts: 3542
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

### Re: Help With Excel Formulas

Regards,
hlewton hlewton
PlatinumLounger
Posts: 3542
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

### Re: Help With Excel Formulas

HansV wrote:
02 Aug 2021, 20:14
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
You do not have the required permissions to view the files attached to this post.
Regards,
hlewton HansV
Posts: 76156
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Help With Excel Formulas

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.
Regards,
Hans hlewton
PlatinumLounger
Posts: 3542
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

### Re: Help With Excel Formulas

Thanks, now I understand it.
Regards,
hlewton hlewton
PlatinumLounger
Posts: 3542
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

### Re: Help With Excel Formulas

=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?
Regards,
hlewton Leif
Posts: 7053
Joined: 15 Jan 2010, 22:52
Location: Middle of England

### Re: Help With Excel Formulas

x.jpg
You do not have the required permissions to view the files attached to this post.
Leif hlewton
PlatinumLounger
Posts: 3542
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

### Re: Help With Excel Formulas

Leif wrote:
26 Aug 2021, 12:56
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
Regards,
hlewton hlewton
PlatinumLounger
Posts: 3542
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

### Re: Help With Excel Formulas

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.
Regards,
hlewton HansV
Posts: 76156
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Help With Excel Formulas

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
You do not have the required permissions to view the files attached to this post.
Regards,
Hans hlewton
PlatinumLounger
Posts: 3542
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

### Re: Help With Excel Formulas

Thank you. It worked great.
Regards,
hlewton StuartR
Posts: 12237
Joined: 16 Jan 2010, 15:49
Location: London, Europe

### Re: Help With Excel Formulas

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")
StuartR hlewton
PlatinumLounger
Posts: 3542
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

### Re: Help With Excel Formulas

StuartR wrote:
26 Aug 2021, 14:27
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.
Regards,
hlewton HansV
Posts: 76156
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Help With Excel Formulas

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.
Regards,
Hans hlewton
PlatinumLounger
Posts: 3542
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

### Re: Help With Excel Formulas

HansV wrote:
26 Aug 2021, 13:58

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?
Regards,
hlewton HansV
Posts: 76156
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Help With Excel Formulas

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.
Regards,
Hans