**Question description:**

*This user has given permission to use the problem statement for this blog.*

I need help with a formula to get the difference between 2 cell and if the cell is empty get the difference between the next populated cell above it. I would like this to be a template for future use.

Solved by G. Q. in 54 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

User
04/10/2018 - 03:24

hello

Excelchat Expert
04/10/2018 - 03:24

Hi…Welcome to Got It Pro. I see that your question is about fixing formulas.

Excelchat Expert
04/10/2018 - 03:25

Could you please illustrate a sample data in the preview sheet?

User
04/10/2018 - 03:26

on sec

Excelchat Expert
04/10/2018 - 03:26

Sure...thanks!

Excelchat Expert
04/10/2018 - 03:27

While you are placing the sample data, this is a quick reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.

User
04/10/2018 - 03:29

can I copy past images in this chat window

Excelchat Expert
04/10/2018 - 03:29

You can upload .jpeg image

Excelchat Expert
04/10/2018 - 03:30

I will just need to understand the structure so that I can build the formula accordingly.

Excelchat Expert
04/10/2018 - 03:31

Let me place some data on this preview sheet and you can let me know if it conforms to your data.

User
04/10/2018 - 03:32

the preview is block by my companys firewall

[Uploaded an Excel file]

Excelchat Expert
04/10/2018 - 03:33

oh ok

Excelchat Expert
04/10/2018 - 03:33

If you happen to be working in excel, you can tell me the cell references that you have data on, I will set up the formula based on that.

User
04/10/2018 - 03:34

can you see the image

Excelchat Expert
04/10/2018 - 03:34

Nope

Excelchat Expert
04/10/2018 - 03:34

Are you not able to upload your excel file?

User
04/10/2018 - 03:35

this

[Uploaded an Excel file]

User
04/10/2018 - 03:36

did you get that

User
04/10/2018 - 03:37

Did that file come through?

Excelchat Expert
04/10/2018 - 03:39

Yes...please give me a moment. The site got frozen on my browser. I am now downloading the file.

User
04/10/2018 - 03:39

ok

Excelchat Expert
04/10/2018 - 03:39

Ok I have the file opened now.

User
04/10/2018 - 03:40

I'm on sheet 2 and looking for a good formula for the c columns

Excelchat Expert
04/10/2018 - 03:40

Could you explain which cell should the formula be and which cells to look for?

Excelchat Expert
04/10/2018 - 03:40

You mean 'https://expert.excelchat.co/home'?

User
04/10/2018 - 03:41

the values in are variable and some days to not have data

Excelchat Expert
04/10/2018 - 03:42

Sorry...that was a typo...I mistakenly placed the website address as I was switching between browser.

Excelchat Expert
04/10/2018 - 03:42

The sheet to refer is South Plant Chemical Usage, right?

User
04/10/2018 - 03:42

I want this sheet to be a template and need the formula in column c to work

User
04/10/2018 - 03:42

yes

User
04/10/2018 - 03:44

i want the difference of the chemical usage during each day that a reading in inputted in to column B

Excelchat Expert
04/10/2018 - 03:44

Ok...so I see a formula already in here.

Excelchat Expert
04/10/2018 - 03:45

Is it not giving you the desired result?

User
04/10/2018 - 03:45

so c8 should be b8 -b4

Excelchat Expert
04/10/2018 - 03:46

Ok..so substract the last filled cell in the column, is that correct?

User
04/10/2018 - 03:46

but if a reading is recorded on b7 then then c8 would be =to b8-b7

Excelchat Expert
04/10/2018 - 03:47

Ok...got you.

User
04/10/2018 - 03:47

yes

Excelchat Expert
04/10/2018 - 03:47

Now I will be working on a formula. This will take me few minutes. Please be with me.

User
04/10/2018 - 03:47

ok

User
04/10/2018 - 03:47

my brain will not connect the dots

User
04/10/2018 - 03:51

hello

User
04/10/2018 - 03:52

i lost power at my office

Excelchat Expert
04/10/2018 - 03:52

Yep..I am here working on a solution.

User
04/10/2018 - 03:52

ok

Excelchat Expert
04/10/2018 - 03:56

Please do extend the time as this may take a little longer.

User
04/10/2018 - 03:56

i did it once already

Excelchat Expert
04/10/2018 - 03:56

Ok...you will get one more extension for free.

User
04/10/2018 - 03:57

ok

Excelchat Expert
04/10/2018 - 04:02

Could you please use this formula in C5?

Excelchat Expert
04/10/2018 - 04:02

=IF(B5="",0,IF(AND(B4<>"",B5<>""),B5-B4,B5-INDEX($B$4:B4,COUNT($B$4:B4))))

Excelchat Expert
04/10/2018 - 04:03

And drag it down

User
04/10/2018 - 04:04

ok

User
04/10/2018 - 04:04

done

User
04/10/2018 - 04:05

I would have never got that!

Excelchat Expert
04/10/2018 - 04:06

Does that work the way you wanted?

User
04/10/2018 - 04:06

let me test a few things

Excelchat Expert
04/10/2018 - 04:06

Sure

User
04/10/2018 - 04:07

ok type 40.5 in b6

User
04/10/2018 - 04:08

b8 should now be the sub of b6

Excelchat Expert
04/10/2018 - 04:08

Ok...it needs a tweak.

Excelchat Expert
04/10/2018 - 04:08

I am looking at it.

Excelchat Expert
04/10/2018 - 04:10

This should now work.

Excelchat Expert
04/10/2018 - 04:10

=IF(B5="",0,IF(AND(B4<>"",B5<>""),B5-B4,B5-LOOKUP(2,1/(1-ISBLANK($B$4:B4)),$B$4:B4)))

Excelchat Expert
04/10/2018 - 04:10

I am using Lookup instead of Count to determine the rows as we have blanks in between.

User
04/10/2018 - 04:11

ok it looks better now

User
04/10/2018 - 04:11

I will have to brake down what you did so i can learn

Excelchat Expert
04/10/2018 - 04:12

[Uploaded an Excel file]

Excelchat Expert
04/10/2018 - 04:12

Let me explain the formula.

Excelchat Expert
04/10/2018 - 04:13

This has nested IF statement.

Excelchat Expert
04/10/2018 - 04:13

1st IF is checking if adjacent in Column B is filled or blank. If blank, it returns a zero.

Excelchat Expert
04/10/2018 - 04:14

2nd IF statement has an AND condition where if we have both the adjacent cell and and one above filled, then it will do the normal substract of the 2 adjacent cells.

User
04/10/2018 - 04:14

ok

Excelchat Expert
04/10/2018 - 04:15

Now the final condition if both the above 2 IF statement fails is to fetch the last filled cell in the column above your reference row and substract that.

Excelchat Expert
04/10/2018 - 04:15

For that I am using a lookup function.

User
04/10/2018 - 04:16

awesome

Excelchat Expert
04/10/2018 - 04:17

Does that help?

User
04/10/2018 - 04:17

Yes very much

User
04/10/2018 - 04:18

Thanks

Excelchat Expert
04/10/2018 - 04:18

Will there be anything else regarding this query?

User
04/10/2018 - 04:18

not today

Excelchat Expert
04/10/2018 - 04:18

Perfect.

Excelchat Expert
04/10/2018 - 04:18

Please feel free to leave any comments or feedback.

Excelchat Expert
04/10/2018 - 04:18

Thanks for your time. Please do come back for any new question. You can now end this session. Have a great day ahead!

User
04/10/2018 - 04:18

ok

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