Calculation The GAP

[apramono]

Hi All,

Please kindly help for the problem. I need to break down and compare data for scan vs data sales.
1. If the data scan > data sales . The calculation will be using data sales
2. If the data scan < data sales . The calculation will be using data scan

Unfortunately data scan separate amount how much

Code	Name	500	400	300	200	100	Total Scan	Data Sales	Calculation
abc	Mr. X	1	0	0	0	5	6	5	=(1*500)+(4*100)
def	Mr. Y	0	2	2	1	0	5	6	=(2*400)+(2*300)+(1*200)

 

[Zot]

Because i will used the lowest number ... total scan 6 and data sales are 5

Store Name	10,000,000	5,000,000	1,000,000	500,000	100,000	50,000	20,000	10,000	5,000	Grand Total	Data Sales	Calculation
TOMS LIGHTING	-	-	-	-	-	-	-	15	-	15	5	= (5*5000)
TOMS LIGHTING 2	-	-	-	-	-	-	1	9	-	10	10	=(1*20.000)+(9*10.000)
CAHAYA AGUNG JAYA ELEKTRIK	-	-	-	-	-	-	-	46	-	46	30	=(30*10.000) caused data sales are 30
Purnama jaya elektrik	-	-	-	-	-	-	-	2	-	2	-	0 cause data sales are 0
DUA BERLIAN TK	-	-	-	1	1	1	-	-	-	3	6	=(1*500.000)+(1*100.000)+(1*50.000)
UD PIJAR MANDIRI	-	-	-	-	-	-	-	11	-	11	11	=(11*10.000)
RADO/SURYA ELEKTRIK	-	-	1	-	-	5	281	478	-	765	325	=(1*1.000.000)+(5*50.000)+(281*20.000)+(38*10.000) (the rest will not calculate because data sales are 325)

I'm assuming the table above Store Name is in cell A1. The formula for Toms Lighting would be:

=IF(L2<=K2,SUMPRODUCT($B$1:$J$1,B2:J2,1*(B2:J2<L2))+SUMPRODUCT($B$1:$J$1,(B2:J2>L2)*(L2-SUMIF(B2:J2,"<"&L2))),SUMPRODUCT($B$1:$J$1,B2:J2))

You can then drag the formula down

 

[apramono]

I'm assuming the table above Store Name is in cell A1. The formula for Toms Lighting would be:

=IF(L2<=K2,SUMPRODUCT($B$1:$J$1,B2:J2,1*(B2:J2<L2))+SUMPRODUCT($B$1:$J$1,(B2:J2>L2)*(L2-SUMIF(B2:J2,"<"&L2))),SUMPRODUCT($B$1:$J$1,B2:J2))

You can then drag the formula down

Hi Zot, Thanks a alot for the feedback. I'll try to implement the formula into my workbook. But some calculation are still in correct.

Arcode	Store Name	10,000,000	5,000,000	1,000,000	500,000	100,000	50,000	20,000	10,000	5,000	Grand Total	Benefit Serbu Vers.	Data Sales	Entitlement
1​	ABC	-	-	-	-	-	-	-	15	-	15	150,000	10	100,000
2​	DEF	-	-	-	-	-	-	1	9	-	10	110,000	-	-
3​	GHI	-	-	-	-	-	-	-	46	-	46	460,000	30	300,000
4​	JKL	-	-	-	-	-	-	-	2	-	2	20,000	-	-
5​	MNO	-	-	-	-	-	-	-	9	-	9	90,000	13	90,000
6​	PWR	-	-	-	-	-	-	-	11	-	11	110,000	-	-
7​	STU	-	-	1	-	-	5	281	478	-	765	11,650,000	494	11,650,000
8​	VWX	-	-	-	-	-	-	-	22	-	22	220,000	113	220,000
9​	YZA	-	-	-	-	-	-	-	1	-	1	10,000	4	10,000
10​	ABD	-	-	2	-	37	80	792	944	-	1,855	34,980,000	5,205	34,980,000
11​	ALS	-	-	-	-	-	-	-	1	-	1	10,000	3	10,000
12​	ATU	-	-	-	-	-	-	-	4	-	4	40,000	6	40,000

For row number 7 it number of entitle are : 8.940.000
Total Data Scan are : 765
Total Data Sales : 494

It was come from

Data Scan	Should	Entitle	Entitle Based Scan	Entitle Final
1​	1​	1,000,000	1,000,000	1,000,000
5​	5​	50,000	250,000	250,000
281​	281​	20,000	5,620,000	5,620,000
478​	207​	10,000	4,780,000	2,070,000
765​	494​		11,650,000	8,940,000

Only 207*10.000 data from 478 data scan

Thanks in advance Zot.

 

[Zot]

I probably copy wrong one. The one I tested OK was without =. L2<K2 instead of L2<=K2
Tested this just fine with data row#7

=IF(L2<K2,SUMPRODUCT($B$1:$J$1,B2:J2,1*(B2:J2<L2))+SUMPRODUCT($B$1:$J$1,(B2:J2>L2)*(L2-SUMIF(B2:J2,"<"&L2))),SUMPRODUCT($B$1:$J$1,B2:J2))

Get 8,940,000 as answer.