**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.*