Averaging & Assigning Blank Cells a Specific Numeric Value

Closed
averagejoem Posts 2 Registration date Thursday July 11, 2013 Status Member Last seen July 12, 2013 - Jul 11, 2013 at 05:56 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 13, 2013 at 12:10 AM
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 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jul 12, 2013 at 07:51 AM
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
averagejoem Posts 2 Registration date Thursday July 11, 2013 Status Member Last seen July 12, 2013
Jul 12, 2013 at 06:24 PM
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
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jul 13, 2013 at 12:10 AM
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