Help With Excel Formulas

 GoldLounger
 Posts: 2817
 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.
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
hlewton

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

 GoldLounger
 Posts: 2817
 Joined: 24 Oct 2010, 23:39
 Location: Canton, Ohio USA
Re: Help With Excel Formulas
Thanks that worked great. Not sure why I couldn't figure out.
Regards,
hlewton
hlewton

 GoldLounger
 Posts: 2817
 Joined: 24 Oct 2010, 23:39
 Location: Canton, Ohio USA
Re: Help With Excel Formulas
=IF(B4="","",(B4A4))  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.
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.
You do not have the required permissions to view the files attached to this post.
Regards,
hlewton
hlewton

 Administrator
 Posts: 72233
 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 dropdown, or by selecting File > Options > Formulas.
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 dropdown, or by selecting File > Options > Formulas.
You do not have the required permissions to view the files attached to this post.
Regards,
Hans
Hans

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

 GoldLounger
 Posts: 2817
 Joined: 24 Oct 2010, 23:39
 Location: Canton, Ohio USA
Re: Help With Excel Formulas
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?HansV wrote: ↑02 Aug 2021, 20:14The 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 dropdown, or by selecting File > Options > Formulas.
S0641.png
You do not have the required permissions to view the files attached to this post.
Regards,
hlewton
hlewton

 Administrator
 Posts: 72233
 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.
=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
Hans

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

 GoldLounger
 Posts: 2817
 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?
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
hlewton

 Administrator
 Posts: 7170
 Joined: 16 Jan 2010, 08:21
 Location: UK/France
Re: Help With Excel Formulas
You do not have the required permissions to view the files attached to this post.
Leif.

 GoldLounger
 Posts: 2817
 Joined: 24 Oct 2010, 23:39
 Location: Canton, Ohio USA
Re: Help With Excel Formulas
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

 GoldLounger
 Posts: 2817
 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.
=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
hlewton

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

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

 Administrator
 Posts: 11564
 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")
=IF(AVERAGE($B$3:$B$400)=0,"",ROUND(AVERAGE($B$3:$B$400),2)&" is Average Days Between Changing Batteries")
StuartR

 GoldLounger
 Posts: 2817
 Joined: 24 Oct 2010, 23:39
 Location: Canton, Ohio USA
Re: Help With Excel Formulas
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
hlewton

 Administrator
 Posts: 72233
 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.
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
Hans

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

 Administrator
 Posts: 72233
 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 semicolons.
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 semicolons or after the second semicolon), negative numbers and zeros will not be displayed.
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 semicolons or after the second semicolon), negative numbers and zeros will not be displayed.
Regards,
Hans
Hans