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.
