Formula required for a data range

[hsandeep]

MATCH.xlsx
	D	E	F	G	H	I	J	K	L
4	40829.99		30		50		40799.99		40750
5	40831		30		50		40801		40800
6	40800		30		50		40770		40750
7	40900		25		60		40875		40840
8	41000		20		70		40980		40930
9	41000.01		10		60		40990.01		40930
Sheet1

Cell Formulas
Range	Formula
J4:J9	J4	=D4-F4

Formula required for L4:L9 please

 

[Fluff]

Care to explain what you are after?

 

[Joe4]

Looking at column L, it looks like maybe you want some sort of "round down" functionality.
But we cannot tell what values to round down to, based on your examples.
I do not see any discernable pattern, so I think you will need to explain it to us.

 

[hsandeep]

Care to explain what you are after?

I’ll try to explain my requirement (in raw language, please excuse me for this):

Consider D4 & arrive at a value (for reference sake I’ll call here new D4) which has FIRST 3 digits of D4 & then suffixed by 00.
Subtract F4 from D4
IF D4-F4<new D4 then ROUNDDOWN or FLOOR the integer value of (new D4-H4) to nearest multiple of H4 with respect to new D4
IF D4-F4>=new D4 then ROUNDDOWN or FLOOR the integer value of (D4-F4) to nearest multiple of H4 with respect to new D4

 

[Fluff]

I'm afraid I am none the wiser, can you post the expected results?

 

[hsandeep]

I'm afraid I am none the wiser, can you post the expected results?

MATCH.xlsx
	D	E	F	G	H	I	J	K	L
2							Changed	Added Helper column
3
4	40829.99		30		50		40800	40799.99	40750
5	40831		30		50		40800	40801	40800
6	40800		30		50		40800	40770	40750
7	40900		25		60		40860	40875	40860
8	41000		20		70		40950	40980	40950
9	41000.01		10		60		40980	40990.01	40980
Sheet1

Cell Formulas
Range	Formula
J4:J9	J4	=FLOOR(D4,H4)
K4:K9	K4	=D4-F4
L4:L9	L4	=IF(K4<J4,J4-H4,J4)

Explaining THE REQUIREMENT (needed in Excel) is sometimes ‘more difficult’ than to solve.
Anyway, I changed the formula of J column & ADDED 1 helper column K column to derive my requirement (in L column), somehow.
Can the formulas be stitched to make a consolidated one?

 

[Fluff]

How about

=IF((D4-F4)<FLOOR(D4,H4),FLOOR(D4,H4)-H4,FLOOR(D4,H4))

 

[hsandeep]

How about

=IF((D4-F4)<FLOOR(D4,H4),FLOOR(D4,H4)-H4,FLOOR(D4,H4))

Superb. Thanks Fluff. Your formula works

 

[Fluff]

Glad we could help & thanks for the feedback.