Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recouping
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recouping
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|