Formula to Subtract two values based on the latest values on Weekdays

[waseem0888]

Hello,

I need a formula to subtract the two latest cells values based on the values in the cells of a particular day.
For example, I did my entries on Saturday in cells D9 and F9 I want to subtract these two values 1100 - 200 which will result in column AP9 1100-200 = 900
So next day I again on Sunday I insert values in I9 and K9 where I want subtraction but the result should be reflected in the same cell No. AP9
this is what I want for my whole week result should be calculated in AP9 and the logical purpose is to avoid inserting several columns for subtraction with each day.
Just want the current day result should be in one column Cell No. AP9.

Thanks,

 

[Peter_SSs]

Welcome to the MrExcel board!

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[waseem0888]

Hello,

I need a formula to subtract the two latest cells values based on the values in the cells of a particular day.
For example, I did my entries on Saturday in cells D9 and F9 I want to subtract these two values 1100 - 200 which will result in column AP9 1100-200 = 900
So next day I again on Sunday I insert values in I9 and K9 where I want subtraction but the result should be reflected in the same cell No. AP9
this is what I want for my whole week result should be calculated in AP9 and the logical purpose is to avoid inserting several columns for subtraction with each day.
Just want the current day result should be in one column Cell No. AP9.

Thanks,

 

[Peter_SSs]

We can't see any column labels or row numbers in that so your description cannot easily be related to the image. Further, we cannot copy paste from an image to test with.

 

[waseem0888]

Hello,

I need a formula to subtract the two latest cells values based on the values in the cells of a particular day.
For example, I did my entries on Saturday in cells D9 and F9 I want to subtract these two values 1100 - 200 which will result in column AP9 1100-200 = 900
So next day I again on Sunday I insert values in I9 and K9 where I want subtraction but the result should be reflected in the same cell No. AP9
this is what I want for my whole week result should be calculated in AP9 and the logical purpose is to avoid inserting several columns for subtraction with each day.
Just want the current day result should be in one column Cell No. AP9.

Thanks,

 

[waseem0888]

Hello,

I need a formula to subtract the two latest cells values based on the values in the cells of a particular day.
For example, I did my entries on Saturday in cells D9 and F9 I want to subtract these two values 1100 - 200 which will result in column AP9 1100-200 = 900
So next day I again on Sunday I insert values in I9 and K9 where I want subtraction but the result should be reflected in the same cell No. AP9
this is what I want for my whole week result should be calculated in AP9 and the logical purpose is to avoid inserting several columns for subtraction with each day.
Just want the current day result should be in one column Cell No. AP9.

Thanks,

Workfront Tracker - R0.1 draft - Copy.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
6	SATURDAY					SUNDAY					MONDAY					TUE					WEDNESDAY					THURSDAY					FRI					FRONT TO BE MAINTAINED			ACTUAL FRONT
7	AVAILABLE FRONT		DAILY PRODUCTION			AVAILABLE FRONT		DAILY PRODUCTION			AVAILABLE FRONT		DAILY PRODUCTION			AVAILABLE FRONT		DAILY PRODUCTION			AVAILABLE FRONT		DAILY PRODUCTION			AVAILABLE FRONT		DAILY PRODUCTION			AVAILABLE FRONT		DAILY PODUCTION
8	QTY.	UNIT	QTY	UNIT		QTY.	UNIT	QTY	UNIT		QTY.	UNIT	QTY	UNIT		QTY.	UNIT	QTY	UNIT		QTY.	UNIT	QTY	UNIT		QTY.	UNIT	QTY	UNIT		QTY.	UNIT	QTY	UNIT		QTY	UNIT		QTY	UNIT
9	1100	lm	200	lm		1000	lm	210	lm		900	lm	160	lm		1000	lm	200	lm		850	lm	150	lm		900	lm	170	lm		1000	lm	200	lm		950	lm		900	lm
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
WORKFRONT TRACKER

Cell Formulas
Range	Formula
AP9	AP9	=D9-F9

 

[Peter_SSs]

Thanks for the XL2BB sample data. Is this the sort of thing you are after?

21 08 18.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
6	SATURDAY					SUNDAY					MONDAY					TUE					WEDNESDAY					THURSDAY					FRI					FRONT TO BE MAINTAINED			ACTUAL FRONT
7	AVAILABLE FRONT		DAILY PRODUCTION			AVAILABLE FRONT		DAILY PRODUCTION			AVAILABLE FRONT		DAILY PRODUCTION			AVAILABLE FRONT		DAILY PRODUCTION			AVAILABLE FRONT		DAILY PRODUCTION			AVAILABLE FRONT		DAILY PRODUCTION			AVAILABLE FRONT		DAILY PODUCTION
8	QTY.	UNIT	QTY	UNIT		QTY.	UNIT	QTY	UNIT		QTY.	UNIT	QTY	UNIT		QTY.	UNIT	QTY	UNIT		QTY.	UNIT	QTY	UNIT		QTY.	UNIT	QTY	UNIT		QTY.	UNIT	QTY	UNIT		QTY	UNIT		QTY	UNIT
9	1100	lm	200	lm		1000	lm	210	lm		900	lm	160	lm																									740
Sheet2 (2)

Cell Formulas
Range	Formula
AP9	AP9	=LET(f,FILTER(D9:AK9,(D$8:AK$8="QTY.")*(D9:AK9<>"")),INDEX(f,COUNT(f))-LOOKUP(9^9,D9:AK9))

 

[waseem0888]

Thanks for the XL2BB sample data. Is this the sort of thing you are after?

21 08 18.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
6	SATURDAY					SUNDAY					MONDAY					TUE					WEDNESDAY					THURSDAY					FRI					FRONT TO BE MAINTAINED			ACTUAL FRONT
7	AVAILABLE FRONT		DAILY PRODUCTION			AVAILABLE FRONT		DAILY PRODUCTION			AVAILABLE FRONT		DAILY PRODUCTION			AVAILABLE FRONT		DAILY PRODUCTION			AVAILABLE FRONT		DAILY PRODUCTION			AVAILABLE FRONT		DAILY PRODUCTION			AVAILABLE FRONT		DAILY PODUCTION
8	QTY.	UNIT	QTY	UNIT		QTY.	UNIT	QTY	UNIT		QTY.	UNIT	QTY	UNIT		QTY.	UNIT	QTY	UNIT		QTY.	UNIT	QTY	UNIT		QTY.	UNIT	QTY	UNIT		QTY.	UNIT	QTY	UNIT		QTY	UNIT		QTY	UNIT
9	1100	lm	200	lm		1000	lm	210	lm		900	lm	160	lm																									740
Sheet2 (2)

Cell Formulas
Range	Formula
AP9	AP9	=LET(f,FILTER(D9:AK9,(D$8:AK$8="QTY.")*(D9:AK9<>"")),INDEX(f,COUNT(f))-LOOKUP(9^9,D9:AK9))

I appreciate your help it works great. But I am facing one issue when I try this sheet on another PC then I was getting #NAME? error and when I check the formula it's changing the function to this =_xlfn.LET(_xlpm.f,FILTER(D9:AK9,(D$8:AK$8="QTY.")*(D9:AK9<>"")),INDEX(_xlpm.f,COUNT(_xlpm.f))-LOOKUP(9^9,D9:AK9)) I am using MS 365 Apps for enterprise but when I check on MS 365 ProPlus it was giving me this error and this sheet will be using many other users which may use a different version of excel 2013, 2016, etc.. so what is the solution for this can you guide me, please.

Thank you.

 

[Peter_SSs]

Yes, that formula for MS 365 only. Try this one for Excel 2010 or later.

=INDEX(D9:AK9,AGGREGATE(14,6,(COLUMN(D9:AK9)-COLUMN(D9)+1)/((D$8:AK$8="QTY.")*(D9:AK9<>"")),1))-LOOKUP(9^9,D9:AK9)