If Statement and AVG help

nacskins

New Member
Joined
Sep 20, 2013
Messages
46
Hello all, looking for a little help. Currently using if statements and not getting what I need displayed. Currently have **** =IF((SUM(C5,C8,C11,C14,C17,C20,C23)=0),"",SUM(C5,C8,C11,C14,C17,C20,C23)) **** which works great, if the cells listed are blank then the display is left blank.... works great. Now I also have ***** =AVERAGE(C6,C9,C12,C15,C18,C21,C24)/1440 **** to which I also need to return a blank if nothing entered in cells listed. Seems like no matter what IF statement I use I get ***** #DIV/0! **** displayed when cells listed are blank. Any help is as always greatly appreciated.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try this if you have XL2007+

=IFERROR(AVERAGE(C6,C9,C12,C15,C18,C21,C24)/1440,"")
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
Hello all, looking for a little help. Currently using if statements and not getting what I need displayed. Currently have **** =IF((SUM(C5,C8,C11,C14,C17,C20,C23)=0),"",SUM(C5,C8,C11,C14,C17,C20,C23)) **** which works great, if the cells listed are blank then the display is left blank.... works great. Now I also have ***** =AVERAGE(C6,C9,C12,C15,C18,C21,C24)/1440 **** to which I also need to return a blank if nothing entered in cells listed. Seems like no matter what IF statement I use I get ***** #DIV/0! **** displayed when cells listed are blank. Any help is as always greatly appreciated.

Something like:

=IFERROR(AVERAGE(C6,C9,C12,C15,C18,C21,C24)/1440,"")
 

nacskins

New Member
Joined
Sep 20, 2013
Messages
46
Beautiful !!!! Thank you very much.... If you have the time I have a 3rd cell that has *** =SUM(C7,C10,C13,C16,C19,C22,C25)/1440 *** and is formatted to display time (h:mm) and if the listed cells are blank we get (0:00). Any suggestions on this to show blank instead?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210

ADVERTISEMENT

beautiful !!!! Thank you very much.... If you have the time i have a 3rd cell that has *** =sum(c7,c10,c13,c16,c19,c22,c25)/1440 *** and is formatted to display time (h:mm) and if the listed cells are blank we get (0:00). Any suggestions on this to show blank instead?

=if(isnumber(1/sum(c7,c10,c13,c16,c19,c22,c25)),sum(c7,c10,c13,c16,c19,c22,c25)/1440,"")
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,968
Messages
5,856,579
Members
431,820
Latest member
martel_9

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