CODEWITHSUNDEEP
excel
DanMerritt
DanMerritt

Reputation: 21

Lookup formula to subtract last input data point

I am trying to write a formula that will subtract my current value from my last data point. I run a production report and over the weekends we do not produce any product. I would like the value typically from a Friday be used for calculating the change in production for Monday.

I have an input page for all the data and then a calculation page for the reports.

(Input page)

Thursday    1000
Friday      5000
Saturday    "blank"
Sunday      "blank"
Monday      2000

Ideally the output page would look something like this:

(output)

Friday      4000
Saturday    "blank"
Sunday      "blank"
Monday     -3000

Having the last inputted data (being the 5000 value from Friday) subtracted from the Monday value of 2000.

Upvotes: 2

Views: 283

Answers (2)

Zooby
Zooby

Reputation: 323

My idea was:

=IF(B2=0,"Nonproductive",IF(B1<>0,B2-B1,B2-OFFSET(B2,-(COUNTIF(OFFSET(C2,-3,0,3),"nonproductive")+1),0)))

Imagine you've got your days in column A, and production in column B.

Copy and paste this from cell C2 onwards.

What this does, is first test if there was any production on a given day, if there was 0 production, or in your above example, "blank", then it will give the value "nonproductive". If there was production on that day, it will test if the day prior had any production, if it did, it will simply deduct yesterdays from today. If the day prior had no production, it will count the number of times that "nonproductive" has occurred in the 3 days prior and add that to the number of days in the past it looks.

On a long weekend, it would count 3 nonproductives, so it would compare Monday to Thursday.

Monday - (1 (default offset for the day prior) + 3 (due to nonproductives)

Monday - 4 = Thursday.

Notes: This will not work for the first 2 rows because the offset will be trying to find cells that don't exist. Even if

The only problems arise when a break greater than 3 days happens, or you have a day off, then a day on, then a day off, then a day on, such as a Tuesday bank holiday, this is because there are 2 "nonproductive" days in the 3 days prior, but we only want it to increase the offset by 1. This could be avoided through continuing a chain of "if" condition checks, but it doesn't sound as though that is required.

This will work for a 2 or 3 day weekend, and some mid-week holidays

Upvotes: 0

pnuts
pnuts

Reputation: 59475

Please try: 

=IF(LEFT(A2,1)="S","",Sheet4!B3-INDEX(Sheet4!B:B,MATCH(1E+100,Sheet4!B$1:B2)))

with adjustment of cell references to suit.

Upvotes: 1

Related Questions