CODEWITHSUNDEEP
excelexcel-formula
www.friend0.in
www.friend0.in

Reputation: 257

How to calculate a value for multiple rows?

The problem is incentive and bonus amount is to be divided among a office may be a odd number, for example let Rs 100 will be divided as sales incentive for a office amoung 3 employees. In this case 2 employees will get 33 and 1 employee will get Rs 34, it does not matter who gets 33 or 34, the only thing matters is that the difference should be at minimum and the sum must not exceed than the allotted amount.
I came up with some basic functions to calculate the amount as
enter image description here

but the problem is how to loop through the employees/rows ?

enter image description here

Upvotes: 0

Views: 136

Answers (1)

Scott Craner
Scott Craner

Reputation: 152465

use for incentive:

 =IF(COUNTIF($A$2:A2,A2)=COUNTIF(A:A,A2),VLOOKUP(A2,F:G,2,FALSE)-SUMIFS($C$1:C1,$A$1:A1,A2),ROUNDDOWN(VLOOKUP(A2,F:G,2,FALSE)/COUNTIF(A:A,A2),0))

I will let you do the Bonus as it is the same formula with a different lookup range in the two vlookups.

enter image description here

Upvotes: 1

Related Questions