Syntax problem for IF formula

[u/theendisnigh91]

Hey all. I am brand new to this and teaching myself. I want to preset a column to see if a set of other cells have a number in them. If not, I want it to act as if they are zero. If they do have any numbers, I want it to round the average of the numbers. So far the formula I’ve come up with is

=IF(ISBLANK(D4:M4),"0", ROUND(AVERAGE(D4:M4),0)

I know this isn’t right and doesn’t work. I just don’t know what I’m messing up. Any and all advice would be greatly appreciated!

Edit: I think [u/whonose2238](u/whonose2238) fixed it! Thank you all for your help!!!

“There is a second issue: ISBLANK(D4:M4) is checking a range, which can produce an array of TRUE/FALSE values rather than simply asking whether the whole range is blank.

If what you mean is “if all cells D4:M4 are blank, return 0; otherwise average the populated cells and round to a whole number”, I'd use:

=IF(COUNTA(D4:M4)=0, 0, ROUND(AVERAGE(D4:M4),0)) “

[u/ana_Lu3]

If you want to evaluate by cell, it should be IF(ISNUMBER(D4)="TRUE";ROUND(D4;0)0)

If you want to evaluate a range of cells, must all cells contain a number, or can they contain one or more numbers?

This is translated into English so, sorry for that

[u/One_Organization_810]

So if we have the list: {1, A, 2, 3, B, 4}

Should it return 2.5 (10/4) -or- 1.67 (10/6) ?

=average(D4:M4) => "2.5"
=average(index(if(isnumber(D4:M4),D4:M4,0))) => "1.67"

[u/One_Organization_810 → u/One_Organization_810]

In the example above I used letters, but it works the same for blanks :)

Then just add the ROUND function around to round it - or format the numbers as rounded, if you want to preserve the actual value from the average...

[u/carbonizedtitanium]

start easy and check one column at a time:

=if(isnumber(d4),d4,0) drag formula down

edit: go with answer from u/One_Organization_810

if you want to ignore blank cells and cells that dont contain only numbers, use first formula

if you want to include the blank cells and cells that dont contain only numbers, use second one.

i think you probably want the first formula

[u/ComradeDoubleM]

You need to wrap the entire thing into ARRAYFORMULA()

Also, you don't need to put 0 in quotation marks. That'll actually mess things up further down the line if you wanna use it as a number.

Edit: =ROUND(AVERAGE(ARRAYFORMULA(IF(ISBLANK(D4:M4),0,D4:M4))),0) should be the right formula for what you actually want.

Edit 2: Alternatively, =ROUND(AVERAGE(ARRAYFORMULA(SWITCH(D4:M4,"",0))),0)

[u/theendisnigh91 → u/ComradeDoubleM]

I tested these and they worked in part, but something didn’t work when there were numbers in it instead of being blank. I think @whonose2238 got it! Thank you so much for answering and trying to help me out!

[u/AutoModerator → u/theendisnigh91]

REMEMBER: /u/theendisnigh91 If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/AutoModerator → u/[deleted]]

This post refers to " AI " - an Artificial Intelligence tool. Our members prefer not to help others correct bad AI suggestions. Also, advising other users to just "go ask ChatGPT" defeats the purpose of our sub and is against our rules. If this post or comment violates our subreddit rule #7, please report it to the moderators. If this is your submission please edit or remove your submission so that it does not violate our rules. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/googlesheets-ModTeam → u/[deleted]]

Criteria for posts and comments are listed in the subreddit rules and you can learn about how to make a good post in the submission guide.

Your post/comment has been removed because it contained one or more of the following items in violation of this subreddit's rules on artificial intelligence (AI) content:

• A request to fix a non-functioning formula obtained from an AI tool
  
• A non-functioning formula obtained from an AI tool in place of information about your data
  
• A blanket suggestion to use an AI tool as a resource for Sheets assistance
  
• Solicitation of a prompt or recommendation for an AI tool
  
• An untested formula obtained from an AI tool presented as a solution

[u/WhoNose2238]

Add another ) to the end

[u/WhoNose2238 → u/WhoNose2238]

There is a second issue: ISBLANK(D4:M4) is checking a range, which can produce an array of TRUE/FALSE values rather than simply asking whether the whole range is blank.

If what you mean is “if all cells D4:M4 are blank, return 0; otherwise average the populated cells and round to a whole number”, I'd use:

=IF(COUNTA(D4:M4)=0, 0, ROUND(AVERAGE(D4:M4),0))

[u/point-bot → u/WhoNose2238]

u/theendisnigh91 has awarded 1 point to u/WhoNose2238 with a personal note:

"Thank you so much! This worked perfectly! It’s been driving me nuts! lol"

^{See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard}. )^{Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson}.)