Question

Please help with this excel questions. I am using office 360

LL А B C с D E 1. Name October Sales November Sales December Sales Comission 2 Den 15000 16000 12000 3 Scott 16000 20000 15000 4. Colin 11000 9000 15000 5 Dustin 20000 10000 16000 6 Mike 25000 8000 13000 7 James 14000 16000 12000 8 Yvette 15000 18000 16000 9 Karl 11000 12000 18000 10 Frank 21000 15000 12000 11 Required 1: Create a single formula in Column E that will return a 10% commission for December sales if the average monthly fourth quarter sales are more than 15000, and 12 DISCIPLINARY ACTION otherwise. 13

Answer 1

Solution

А. 1 Name 2 Den 3 Scott 4 Colin 5 Dustin 6 Mike 7 James 8 Yvette 9 Karl 10 Frank B D E F October Sales November Sale December Sales Comission Formulla Used in G column 15000 16000 12000 0 =IF(AVERAGE(B2:D2)>15000,10%*D2,0) 16000 20000 15000 1500 =IF(AVERAGE(B3:D3>15000, 10%*D3,0) 11000 9000 15000 0 =IF(AVERAGE(B4:04)>15000, 10%*D4,0) 20000 10000 16000 16001 =IF(AVERAGE(B5:05)>15000,10%*D5,0) 25000 8000 13000 1300 =IF(AVERAGE(B6:D6>15000, 10%*D6,0) 14000 16000 12000 0 =IF(AVERAGE(B7:D7)>15000,10%*D7,0) 15000 18000 16000 1600 =IF(AVERAGE(B8:D8>15000, 10%*D8,0) 11000 12000 18000 0 =IF(AVERAGE(B9:09)>15000,10%*D9,0) 21000 15000 12000 1200=IF(AVERAGE(B10:D10)>15000,10%*D10,0)

B PowerPoint А 1 Name 2 Peter 3 Paul 4 Mary 5 Thomas 6 George 7 Nancy 8 Sinclair 9 Bob 10 Dustin 11 Derek 12 Phoebe С D E F Word Excel Access Outlook 70 50 100 501 70 80 80 70 80 80 90 50 50 60 70 90 70 70 70 80 20 80 80 70 80 70 90 50 70 70 100 80 60 90 50 50 60 90 80 90 100 100 1001 601 G Status 100 Non-Expert 50 Expert 100 Expert 90 Non-Expert 70 Expert 80 Non-Expert 50 Non-Expert 80 Expert 80 Non-Expert 70 Non-Expert 80 Expert H Formulla Used in G column =IF(AND(AND(B2>=70,C2>=70,D2>=70),OR(E2>=70,F2>=70)),"Expert", "Non-Expert") =IF(AND(AND(B3>=70,C3>=70,D3>=70),OR(E3>=70,F3>=70)),"Expert","Non-Expert") =IF(AND(AND(B4>=70,C4>=70,D4>=70),OR(E4>=70,F4>=70)),"Expert", "Non-Expert") =IF(AND(AND(B5>=70,C5>=70,D5>=70),OR(E5>=70,F5>=70)), "Expert", "Non-Expert") =IF(AND(AND(B6>=70,C6>=70,D6>=70),OR(E6>=70,F6>=70)), "Expert", "Non-Expert") =IF(AND(AND(B7>=70,C7>=70,D7>=70),OR(E7>=70,F7>=70)), "Expert", "Non-Expert") =IF(AND(AND(B8>=70,C8>=70,D8>=70),OR(E8>=70,F8>=70)),"Expert", "Non-Expert") =IF(AND(AND(B9>=70,09>=70,09>=70),OR(E9 >=70,F9>=70)), "Expert", "Non-Expert") =IF(AND(AND(B10>=70,C10>=70,D10>=70),OR(E10>=70,F10>=70)), "Expert", "Non-Expert") =IF(AND(AND(B11>=70,C11>=70,D11>=70),OR(E11>=70,F11 >=70)), "Expert", "Non-Expert") =IF(AND(AND(B12>=70,C12>=70,D12>=70),OR(E12>=70,F12>=70)), "Expert", "Non-Expert")

Formula has been shown in next column