Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Hi, I need one example for below condition If cell value Null/Zero how to move automatically to another cell which contains the value this will repeat when the value or condition ends till then it should work
Solved by F. H. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 17/08/2018 - 03:52
Hi
User 17/08/2018 - 03:53
HI
Excelchat Expert 17/08/2018 - 03:53
Can you explain your requirement with an example
User 17/08/2018 - 03:53
Can i write it in the example
User 17/08/2018 - 03:53
Excel
Excelchat Expert 17/08/2018 - 03:53
yes
User 17/08/2018 - 03:53
Okay
User 17/08/2018 - 03:55
will able to see
Excelchat Expert 17/08/2018 - 03:55
yes
Excelchat Expert 17/08/2018 - 03:55
so what are you trying to achive with the formula
User 17/08/2018 - 03:55
Yes the cell which has Zero i need to ignore them and move to next cell
Excelchat Expert 17/08/2018 - 03:55
ok to do what?
User 17/08/2018 - 03:56
I need to take the avg of the values
Excelchat Expert 17/08/2018 - 03:56
you will have 0's or null?
User 17/08/2018 - 03:56
Both
User 17/08/2018 - 03:56
some time cell cotains null/0
Excelchat Expert 17/08/2018 - 03:56
hmm 1 min "Null" as in words or just blank with no data
User 17/08/2018 - 03:56
Blank
User 17/08/2018 - 03:57
And word also i need to know
Excelchat Expert 17/08/2018 - 03:57
hmm like B2 in the excel
User 17/08/2018 - 03:58
yes
User 17/08/2018 - 03:59
the actaul formula is comparision of current value for exaple D6 vs avg of past 5 values that is (D1 to D5)
Excelchat Expert 17/08/2018 - 03:59
1 min
User 17/08/2018 - 03:59
Okay
Excelchat Expert 17/08/2018 - 04:05
check column G
User 17/08/2018 - 04:06
k
User 17/08/2018 - 04:07
Okay
User 17/08/2018 - 04:07
This is fine
User 17/08/2018 - 04:08
i need some thing else
Excelchat Expert 17/08/2018 - 04:08
yes
Excelchat Expert 17/08/2018 - 04:09
you there
User 17/08/2018 - 04:09
If D2 is Null , in place of D2 D3 value should display but nwme should not chane it should remeain D2 only in header
Excelchat Expert 17/08/2018 - 04:11
we cannot write a formula in cell B2 to check its own values
Excelchat Expert 17/08/2018 - 04:11
since it will not have a value but only formula
Excelchat Expert 17/08/2018 - 04:11
now b2 is 19 right
Excelchat Expert 17/08/2018 - 04:11
i mean b1
User 17/08/2018 - 04:11
Yes
Excelchat Expert 17/08/2018 - 04:11
a2 =19 its a value
User 17/08/2018 - 04:11
yes
Excelchat Expert 17/08/2018 - 04:12
you can write a formula in another cell but not in a2
User 17/08/2018 - 04:12
Ha from B2
Excelchat Expert 17/08/2018 - 04:13
you cannot write a formula in B2 they have values
User 17/08/2018 - 04:13
i need to get, why bcz, I am takeing valuse from different Sheet
Excelchat Expert 17/08/2018 - 04:13
oh then it can be done not the same cell
User 17/08/2018 - 04:13
ther i dont have contues values some of them r blank and some of them are null so i need contues format
Excelchat Expert 17/08/2018 - 04:14
you need to give a better example of the data
User 17/08/2018 - 04:14
Okay i will give u
User 17/08/2018 - 04:14
can i share my data sheet
Excelchat Expert 17/08/2018 - 04:14
sure
Excelchat Expert 17/08/2018 - 04:14
just attach it here in the chat window
User 17/08/2018 - 04:14
so that u can have better understand
Excelchat Expert 17/08/2018 - 04:15
yes that would be better and you will not have to change the formula again
User 17/08/2018 - 04:16
I just attached can u plz check
[Uploaded an Excel file]
Excelchat Expert 17/08/2018 - 04:16
sure
Excelchat Expert 17/08/2018 - 04:18
ok which tab
Excelchat Expert 17/08/2018 - 04:18
is your actual data and where do yo uwant to get it
User 17/08/2018 - 04:18
2 and 3rd tabs
Excelchat Expert 17/08/2018 - 04:19
Base Measures and Metrics - Sprint
User 17/08/2018 - 04:19
2 sheet is the base and in 3rd sheet i need to get the data
User 17/08/2018 - 04:19
yes
Excelchat Expert 17/08/2018 - 04:19
from which column to whcih colun
Excelchat Expert 17/08/2018 - 04:20
f5 onwards from c1 to down right?
Excelchat Expert 17/08/2018 - 04:20
so instead of =' Base Measures'!C10 you need a way to skip the 0's and null?
User 17/08/2018 - 04:20
yes
Excelchat Expert 17/08/2018 - 04:20
:) give me few min
User 17/08/2018 - 04:20
Okay
User 17/08/2018 - 04:22
In metrics- Sprint , F5 i need to display the sprint number that is htere in the base mesure that is starting form C10
User 17/08/2018 - 04:23
Like that i need contnuely without breaks in the Metrics sprint tab
Excelchat Expert 17/08/2018 - 04:23
yes 1 min building the formula so if sprint number is 0 or null you want to get the next one
Excelchat Expert 17/08/2018 - 04:23
right
User 17/08/2018 - 04:24
yes which we need to consider as a 2nd sprint like that contnuesly
Excelchat Expert 17/08/2018 - 04:30
still getting the formula
User 17/08/2018 - 04:30
k
User 17/08/2018 - 04:43
R u there ?
Excelchat Expert 17/08/2018 - 04:43
yes we need to transpose them as well thats where i'm stuck
User 17/08/2018 - 04:43
Okay
Excelchat Expert 17/08/2018 - 04:48
[Uploaded an Excel file]
Excelchat Expert 17/08/2018 - 04:49
i have added a helper rows from A38
Excelchat Expert 17/08/2018 - 04:49
the problem is functions like offset and address cannot be used in array formula and we need array formula to get the next null cell
User 17/08/2018 - 04:49
Okay
Excelchat Expert 17/08/2018 - 04:50
so 1st 1 get all the non blank cells then transpose them
Excelchat Expert 17/08/2018 - 04:50
does the formula look good
Excelchat Expert 17/08/2018 - 04:50
you can hide those rows so that they aren't visible
User 17/08/2018 - 04:51
bALNS ARE NOT TAKING RIGHT
Excelchat Expert 17/08/2018 - 04:51
yes
User 17/08/2018 - 04:51
oKAY
Excelchat Expert 17/08/2018 - 04:51
they arent
User 17/08/2018 - 04:51
Tnq I wll see
Excelchat Expert 17/08/2018 - 04:51
you can see all blanks are ignored
User 17/08/2018 - 04:52
Okay Tnq So Much

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.