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.
