Averaging & Assigning Blank Cells a Specific Numeric Value

Closed
Report
Posts
2
Registration date
Thursday July 11, 2013
Status
Member
Last seen
July 12, 2013
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Good day everyone. I'm sorry if this seems remedial, but I'm having trouble with a spreadsheet. Here's the gist of my problem: Within a row, I have series of columns that I want to display the average of all of the numbers. However, there are numerous blank cells in the range. So I would also like to assign a value of "201" to each blank cell, which is then to be factored in the row's average? I hope I explained that well? If anyone can assist, I would be grateful. Thank you for your time.

3 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
suppose your data is in A1 to E5
try this macro

if data is differnt modify r=

Sub fillin_blanks()
Dim r As Range
Set r = Range("A1:E7")
r.SpecialCells(xlCellTypeBlanks) = 201

End Sub


FEEDBACK PLEASE
0
Posts
2
Registration date
Thursday July 11, 2013
Status
Member
Last seen
July 12, 2013

Your suggestion is greatly appreciated, but unfortunately it is over my head as well. I was hoping for a nested formula that I could paste at the end of each row? I'll try your suggestion, but I don't have much experience with macros. Plus when I'm finished I was going to pass this spreadsheet onto someone else with even less Excel experience. I do thank you and appreciate the reply.
0
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
highlight the data

1.click function key F5
2.click "special" in left bottom
3. choose blanks
4.click ok
all the blanks cells will be selected
4.in the first cell enter any value e.g. 121. DO NOT HIT ENTER
5.HIT CONTROL+ENTER

DO U GET WHAT YOU WANT?
0