Recouping

[William Okumu]

Hi I have the following

Category A1 A2 T1 T2
a 4 8 5 5
b 5 6 5 5
C 6 5 5 5
d 7 5 5 5
Total 22 23 20 20

A1 & A2 are Actual Qtr1 and Qtr2
T1 & T2 are Target Qtr1 and Qtr 2

I want a function to be in a cell eg A7 that will evaluate A1 by comparing the individual category in A1 with T1 if the Individual Category are >=T1 the it should Take The Total and mutiply it by 3% which is this case should be 0.66 taking into account that Total A1>=Total T1( if not then should return 0) but if any category in A1<T1 and Total A1>=Total T1then it should Take Total A1 multiply it by 2%(0.44).

In another Cell eg A8 we should have a function that evaluates if A7 divided by Total A1<3% if true then it should evaluate category summation of each category A1 and A2 with summation of each Category T1 and T2 if (A1+A2)>=(T1+T2) then it should Take (Total A1+Total A2)*3% minus Cell A7.And if A7 divided by Total A1=3% then it should evaluate A2 by comparing the individual category in A2 with T2 if the Individual Category are >=T2 then it should Take The Total A2 and mutiply it by 3% which is this case should be 0.69 taking into account that Total A2>=Total T2( if not then should return 0) but if any category in A2<T2 and Total A2>=Total T2then it should Take Total A2 multiply it by 2%(0.46).

[Anna]

Not sure your 2nd paragraph Question. I guess your 1st paragraph answers as
below:

=IF(AND(A1>=T1,B5>=D5),B6*3%)
COME OUT 0.66

=IF(AND((OR((A1>=T1),(A2>=T2),(A3>=T3),(A4>=T4))),(B6>=D6)),B6*2%)
COME OUT 0.44

Hope this help

"William Okumu" wrote:

>
> Hi I have the following
>
> Category A1 A2 T1 T2
> a 4 8 5 5
> b 5 6 5 5
> C 6 5 5 5
> d 7 5 5 5
> Total 22 23 20 20
>
> A1 & A2 are Actual Qtr1 and Qtr2
> T1 & T2 are Target Qtr1 and Qtr 2
>
> I want a function to be in a cell eg A7 that will evaluate A1 by
> comparing the individual category in A1 with T1 if the Individual
> Category are >=T1 the it should Take The Total and mutiply it by 3%
> which is this case should be 0.66 taking into account that Total
> A1>=Total T1( if not then should return 0) but if any category in A1<T1
> and Total A1>=Total T1then it should Take Total A1 multiply it by
> 2%(0.44).
>
> In another Cell eg A8 we should have a function that evaluates if A7
> divided by Total A1<3% if true then it should evaluate category
> summation of each category A1 and A2 with summation of each Category T1
> and T2 if (A1+A2)>=(T1+T2) then it should Take (Total A1+Total A2)*3%
> minus Cell A7.And if A7 divided by Total A1=3% then it should evaluate
> A2 by comparing the individual category in A2 with T2 if the Individual
> Category are >=T2 then it should Take The Total A2 and mutiply it by 3%
> which is this case should be 0.69 taking into account that Total
> A2>=Total T2( if not then should return 0) but if any category in A2<T2
> and Total A2>=Total T2then it should Take Total A2 multiply it by
> 2%(0.46).
>
>
> --
> William Okumu
> ------------------------------------------------------------------------
> William Okumu's Profile: http://www.excelforum.com/member.php...o&userid=33745
> View this thread: http://www.excelforum.com/showthread...hreadid=562021
>
>