FORMULA CORRECTION

[annadinesh]

PMS Report - E3406	25.02.2025
Target	Achievment	Balance	Required /Day
120	77	43	7.00

Date is on D1
target value for the month in on A3
Acheivement as on date is on B3
Balance target is on C3 (A3-B3)

Required Per Day in on D3.

I have a formula on D3 is =ROUNDUP(C3/(EOMONTH(D1,0)-D1-SUMPRODUCT(-(WEEKDAY(ROW(INDIRECT(EOMONTH(TODAY(),-1)+1&":"&EOMONTH(TODAY(),0))))=1))),0)

as per formula the per day requirement to achieve the target is showing 7

but as only 3 days left to end February Month. as per balance target the per day requirement should be 15 (roundup value), but it showing 7

please correct the formula or provide another formula


regards


Dinesh

 

[MARK858]

Maybe...

Book1xxxx.xlsb
	A	B	C	D
1	PMS Report - E3406	25/02/2025
2	Target	Achievment	Balance	Required /Day
3	120	77	43	15.00
Sheet9

Cell Formulas
Range	Formula
B1	B1	=TODAY()
D3	D3	=ROUNDUP(C3/(NETWORKDAYS(B1,EOMONTH(B1,0))-1),0)

 

[sofas]

Other solutions that may suit you

=ROUNDUP(C3 / MAX(1, EOMONTH(B1, 0) - B1 - SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B1+1&":"&EOMONTH(B1, 0)))) = 1))), 0)


=ROUNDUP(C3 / (EOMONTH(B1, 0) - B1), 0)