Function help

[valerie]

I need to know what is the function used in this case;
for e.g indicating “NIL” if the remark is bad, otherwise the salesman
will receive 20% of total salary and bonus as special bonus which this
formula have to be indicated under special column.
(“GOOD” remarks mean the salesman gets a bonus in the yr, otherwise it
would be “BAD” remarks, indicating “YES” in reward column means having
=10% of salesman’s salary under Bonus column, if not, he would not have
any bonus and “YES” in Reward column if the total salary of a salesman
is more or equal to the average salary of the year, otherwise it would
be “BAD”) This is in descending order
Could anyone give me an example of the function formula as well.(The
salary, bonus, etc can be assumedly)
Thanks for any help given.

 

[Max]

It's a little tough <g> to grasp what you're after
despite several readings of your post,
but here's one take on it:

ok, assume you have a sample set-up in A2:G3 as below:

Name Salary Bonus SpecialBonus TotalSalary Remarks Reward
AB 10000 0 2000 12000 Good
BC 20000 2000 Nil 22000 Bad Yes

where cols C, D, E & G require formulae to fit the specs, viz:
col C = Bonus
col D = SpecialBonus
col E = TotalSalary
col G = Reward

with cols A, B & F populated by values

i've also assumed that:
a. TotalSalary = Salary + Bonus + SpecialBonus
b. "Average salary" = Average (TotalSalary col)

Steps

Click Tools > Options > Calculation tab
Check the box for Iterations > OK
(this is necessary because of the circular formula in E2)

Put in:
C2: =IF(G2="Yes",B2*10%,0)
D2: =IF(F2="Good",SUM(B2:C2)*20%,"Nil")
E2: =SUM(B22)
G2: =IF(E2>=AVERAGE($E$2:$E$3),"Yes","")

Copy the formulae in row 2 down the cols C, D, E

For formula in G2, amend the last cell, $E$3,
in "... >=AVERAGE($E$2:$E$3)..."
to suit (I've only assumed 2 rows in the sample set-up above)
before copying down col G

Hope the above helps.

Max

 

[Max]

oops, sorry, there's a typo..

.... sample set-up in A2:G3 as below:...

should read

.... sample set-up in A1:G3 as below:...

 

[Max]

And yet another typo, sheesh..

....(this is necessary because of the circular formula in E2)

should read

....(this is necessary because of the circular formula in G2)

 

[valerie]

My apologies..for my inclarity of words. Nevertheless, Im glad that u
have understood by what I said. Btw, what exactly do u mean for the
formula amendment in G2 and how ya get the formula for Remarks if
salesman get bonus in the yr, indicate "GOOD", otherwise it would be
"BAD". Thanks again.

 

[Max]

Btw, what exactly do u mean for the

formula amendment in G2

Supposing your last row of data is say, row 200,
the formula in G2 needs to be amended to:

=IF(E2>=AVERAGE($E$2:$E$200),"Yes","")

before you copy down col G

how ya get the formula for Remarks if
salesman get bonus in the yr, indicate "GOOD", otherwise it would be
"BAD".

I read it that the Remarks col is one that will contain data keyed-in,
not a formula. The key word in Remarks col will be "Good", which
will trigger the calculation in SpecialBonus col. Anything else
in Remarks col will only return "Nil" in SpecialBonus col.

 

[valerie]

What do you mean by data keyed-in? As far as I know, Remarks itself have
a formula, cos theres the condition that if salesman get a bonus in the
yr, theres "GOOD" indication, otherwise "BAD". But I cant seemed to get
the correct formula myself, thats all.

 

[Max]

Put in F2: =IF(C2>0,"Good","Bad")
copy down col F

Notes:

col C = Bonus
col F = Remarks

 

[valerie]

Thanks max, u have probably solved most of my queries. But I have one
yet to be figured out. You see, I have to calculate the Gross Salary
for the salesman which is Total+Bonus+Special. My Total salary, Bonus
and Special is in B32, B39 and B41 column respectively. As they are not
in consecutive columns, I have used absolute addressing instead of the
function in this case. As such, this is how my formula goes;
=$B$32+$B$39+$B$41. But it comes out as #VALUE! when I pressed Enter.
So what is the problem here?

 

[Max]

Use either : =SUM(B32,B39,B41)

or : =SUM($B$32,$B$39,$B$41)

instead of : =$B$32+$B$39+$B$41

The error is not because of absolute addressing but probably because
one or more of the cells B32, B39, B41 contain text (e.g.: "Nil")
instead of numbers.

The SUM() function ignores text.

 

[valerie]

Oh yeah..it works in that way. Thank you very much for your posting.

 

[Max]

you're welcome, valerie! cheers.