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]

Not sure where =(1*500)+(4*100) this comes about.

You meant compare the Total Scan value vs Data Sales value in respective row? I don't see how this relates to calculation

 

[Peter_SSs]

As well as clarifying Zot's comments, can you please confirm what should happen if Scan = Sales

Perhaps another 5 or 6 rows of sample data and calculation steps might also help clarify.

 

[apramono]

Not sure where =(1*500)+(4*100) this comes about.

You meant compare the Total Scan value vs Data Sales value in respective row? I don't see how this relates to calculation

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)

 

[Zot]

Now I get more confused ?

1st row 5*5000 (5 is Data sales but why multiply with 5000?)
2nd row Grand Total = Data sales (So, same as Data Sales < Grand Total, right. I see number multiply with corresponding column value at the top)
3rd row Data Sales < Grand Total (Use 30 * 10000 column since got value in that column, right?)
4th row (As stated)
5th row Grand Total = Data sales (So, same like 2nd row)
6th row Data Sales < Grand Total like 3rd row (Why use formula like Grand Total < Data Sales ? =(1*1.000.000)+(5*50.000)+(281*20.000)+(38*10.000). Where 38 comes from? )

 

[apramono]

Now I get more confused ?

1st row 5*5000 (5 is Data sales but why multiply with 5000?)
2nd row Grand Total = Data sales (So, same as Data Sales < Grand Total, right. I see number multiply with corresponding column value at the top)
3rd row Data Sales < Grand Total (Use 30 * 10000 column since got value in that column, right?)
4th row (As stated)
5th row Grand Total = Data sales (So, same like 2nd row)
6th row Data Sales < Grand Total like 3rd row (Why use formula like Grand Total < Data Sales ? =(1*1.000.000)+(5*50.000)+(281*20.000)+(38*10.000). Where 38 comes from? )

1st row : sorry it was mistake it should 5*10.000
2nd row : yes it was multiply with corresponding column value at the top
3rd row : yes correct
4rd row : as stated
5th row : same as 2nd row
6th row : 38 comes from total Sales are 325. 325-(1+5+281) and the rest will not count.

 

[Zot]

1st row : sorry it was mistake it should 5*10.000
2nd row : yes it was multiply with corresponding column value at the top
3rd row : yes correct
4rd row : as stated
5th row : same as 2nd row
6th row : 38 comes from total Sales are 325. 325-(1+5+281) and the rest will not count.

So, the 478 is not calculated because it is more than 325 (Data Sales). The condition is also applied if the number is equal to Data sales like in Row 1 in initial example =(1*500)+(4*100)

 

[apramono]

So, the 478 is not calculated because it is more than 325 (Data Sales). The condition is also applied if the number is equal to Data sales like in Row 1 in initial example =(1*500)+(4*100)

Yes form data 478 only 38 will count so (38*10.000) not (478*10.000)

 

[Zot]

Yes form data 478 only 38 will count so (38*10.000) not (478*10.000)

Another condition is:
Say the condition is Data Sales < Grand Total. What if there are more than one column with numbers more than Data Sales?

This is quite complicated... at least for me ?

 

[apramono]

Another condition is:
Say the condition is Data Sales < Grand Total. What if there are more than one column with numbers more than Data Sales?

This is quite complicated... at least for me ?

If Data Sales < Data Scan : The Calculation is from data Data Sales
If Data Sales > Data Scan : The Calculation is form Data Scan.

Unfortunately I need to separate the GAP between criteria in each column in data scan as per table previously. Max number entitle that will be count are from the lowest between Data Sales or Data Scan.