Help With Excel Formulas

User avatar
hlewton
GoldLounger
Posts: 2817
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by 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.
Regards,
hlewton

User avatar
StuartR
Administrator
Posts: 11564
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Help With Excel Formulas

Post by StuartR »

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


User avatar
hlewton
GoldLounger
Posts: 2817
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

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

User avatar
hlewton
GoldLounger
Posts: 2817
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by 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
You do not have the required permissions to view the files attached to this post.
Regards,
hlewton

User avatar
HansV
Administrator
Posts: 72233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Help With Excel Formulas

Post by 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
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

User avatar
hlewton
GoldLounger
Posts: 2817
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

Thank you for the answers.
Regards,
hlewton

User avatar
hlewton
GoldLounger
Posts: 2817
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

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

User avatar
HansV
Administrator
Posts: 72233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Help With Excel Formulas

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

User avatar
hlewton
GoldLounger
Posts: 2817
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

Thanks, now I understand it.
Regards,
hlewton

User avatar
hlewton
GoldLounger
Posts: 2817
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by 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?
Regards,
hlewton

User avatar
Leif
Administrator
Posts: 7170
Joined: 16 Jan 2010, 08:21
Location: UK/France

Re: Help With Excel Formulas

Post by Leif »

x.jpg
You do not have the required permissions to view the files attached to this post.
Leif.

User avatar
hlewton
GoldLounger
Posts: 2817
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

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

User avatar
hlewton
GoldLounger
Posts: 2817
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by 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.
Regards,
hlewton

User avatar
HansV
Administrator
Posts: 72233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Help With Excel Formulas

Post by 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
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

User avatar
hlewton
GoldLounger
Posts: 2817
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

Thank you. It worked great.
Regards,
hlewton

User avatar
StuartR
Administrator
Posts: 11564
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Help With Excel Formulas

Post by 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")
StuartR


User avatar
hlewton
GoldLounger
Posts: 2817
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

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

User avatar
HansV
Administrator
Posts: 72233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Help With Excel Formulas

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

User avatar
hlewton
GoldLounger
Posts: 2817
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

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

User avatar
HansV
Administrator
Posts: 72233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Help With Excel Formulas

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