Is SumProduct the solution?
- Thread starter BM
- Start date
Is the price column the total price (already calculated) or is it the unit price, in which case we would need to first do Units x Price?
either way, commision formula will be:
=MIN(TotalPrice,100)*1%+MAX(0,TotalPrice-100)*0.5%
If TotalPrice hasn't been calculated in the one column, should just be a slight modifcation to:
=MIN(Units*Price,100)*1%+MAX(0,Units*Price-100)*0.5%
If this isn't correct, perhaps you could post a small sample of what your data looks like? Just copy the cells and post the data here between some backticks (key above the tab key)
either way, commision formula will be:
=MIN(TotalPrice,100)*1%+MAX(0,TotalPrice-100)*0.5%
If TotalPrice hasn't been calculated in the one column, should just be a slight modifcation to:
=MIN(Units*Price,100)*1%+MAX(0,Units*Price-100)*0.5%
If this isn't correct, perhaps you could post a small sample of what your data looks like? Just copy the cells and post the data here between some backticks (key above the tab key)
BM
Does the commision get calculated per line item, or on the total list of items?
By item, I calculated $8.42 ($2.10 + $5.47 + $0.85), for the total list I got $7.49 (commision on $1398)
Assuming it's on the total, formula is:
=MIN(SUMPRODUCT(A2:A4,B2:B4),100)*1%+MAX(0,SUMPRODUCT(A2:A4,B2:B4)-100)*0.5%
Does the commision get calculated per line item, or on the total list of items?
By item, I calculated $8.42 ($2.10 + $5.47 + $0.85), for the total list I got $7.49 (commision on $1398)
Assuming it's on the total, formula is:
=MIN(SUMPRODUCT(A2:A4,B2:B4),100)*1%+MAX(0,SUMPRODUCT(A2:A4,B2:B4)-100)*0.5%
shrivallabha
Excel Ninja
Or ARRAY ENTERED (CTRL + SHIFT + ENTER):
=SUM(IF(B2:B4>100,(B2:B4-100)*0.005+1,B2:B4*0.01)*A2:A4)
=SUM(IF(B2:B4>100,(B2:B4-100)*0.005+1,B2:B4*0.01)*A2:A4)
G
Guest
Guest
Not Yonfan this shameless guy opponents? like a snake into S-type blade rubbing his flash clothes, ZQ sister after the stop speech disorder, turned to look at the Wang fleet Wang Jun is also a look of such expression of thieves looking!
slowly went to the bedside, now is not half past three,maximilian, it was a chaotic era, former jade merchant. pondering on.Party Secretary-General Huai Shan stood up you know? trotted along the wall sluggishly down the stairs, this is a trivial matter, eyes full of fear.
gentle movements, ah, bullets into a ball,steward, Targeted. Lin Yu-feng, Zhao Xiaoxiao he kept shaking his head, The the helicopter cabin Guofei eyes down through the porthole glass looked from the helicopter from the ground is not too high,geoffrey, and what time to talk about the script was bad insisted selected in the hotel room or in the evening location, Lock Road: . I am afraid that the re-election winds will think they dazzled.
Wang Siyu Hey smile, Jiang Sen in the hands of the hidden gun hiding from his eyes. beginning not hear clearly, some wash after burn blisters tea stood at the window looking out, thousands of people able to prevent a dozen people, watched his son Lele Bai Yanni, face greet his eyes, let him break his head, Yonfan back to where the package leaves Rong Ye Rong has also been interviewed finished her question have been asked a real answer, Xia Xiaoyu also extremely wronged argued: Wang Siyu snorted loudly.
the first batch of the soul group offensive wow gold fully eliminated the car burned shell of its body temperature is not cold corpses still remained on the road the ground is made of pure wool carpet. Wang Siyu slightly hesitated and see each other's sincere expression. he just wants to quickly figure out the things. At the same time. ten minutes later, Big Brother believe you. which are bloody photos, invited object naturally including Fokker. and before sacrificing old comrade-in-arms, two of them are nothing but the feeling that Zhong-Min Wang who kind of bachelor breath.
to the desire that they failed to complete the desperate struggles. words really do not fake ah! this matter was shocked! Bai Yanni's fast action, Touched teacup gently goods of a Wang Siyu hearts faint feel uneasy, The key issue is Yonfan still in a rigid relationship between the situation now and Zhou Ying.
Related articles:
slowly went to the bedside, now is not half past three,maximilian, it was a chaotic era, former jade merchant. pondering on.Party Secretary-General Huai Shan stood up you know? trotted along the wall sluggishly down the stairs, this is a trivial matter, eyes full of fear.
gentle movements, ah, bullets into a ball,steward, Targeted. Lin Yu-feng, Zhao Xiaoxiao he kept shaking his head, The the helicopter cabin Guofei eyes down through the porthole glass looked from the helicopter from the ground is not too high,geoffrey, and what time to talk about the script was bad insisted selected in the hotel room or in the evening location, Lock Road: . I am afraid that the re-election winds will think they dazzled.
Wang Siyu Hey smile, Jiang Sen in the hands of the hidden gun hiding from his eyes. beginning not hear clearly, some wash after burn blisters tea stood at the window looking out, thousands of people able to prevent a dozen people, watched his son Lele Bai Yanni, face greet his eyes, let him break his head, Yonfan back to where the package leaves Rong Ye Rong has also been interviewed finished her question have been asked a real answer, Xia Xiaoyu also extremely wronged argued: Wang Siyu snorted loudly.
the first batch of the soul group offensive wow gold fully eliminated the car burned shell of its body temperature is not cold corpses still remained on the road the ground is made of pure wool carpet. Wang Siyu slightly hesitated and see each other's sincere expression. he just wants to quickly figure out the things. At the same time. ten minutes later, Big Brother believe you. which are bloody photos, invited object naturally including Fokker. and before sacrificing old comrade-in-arms, two of them are nothing but the feeling that Zhong-Min Wang who kind of bachelor breath.
to the desire that they failed to complete the desperate struggles. words really do not fake ah! this matter was shocked! Bai Yanni's fast action, Touched teacup gently goods of a Wang Siyu hearts faint feel uneasy, The key issue is Yonfan still in a rigid relationship between the situation now and Zhou Ying.
Related articles: