Question

i recreated the tables in excel with the information needed to find the rest as shown of the data that appears in the question. please write formulas that will provide the info in the open cells and also in the commissions lookup table, define the commissions to give a certain % that falls in the same range, for instance if cell E5 value is changed to 1,500,000, the F5 commision cell should change to 4%

Answer 1

I have worked in excel, the formulas displayed will use cell references as displayed in question image. i.e. A3 is irons, B3 is 19750 and so on... Apply the formulas i am sharing in your excel workbook and you will get the final answers.

Monthly Sales				Commission Lookup
Name	Sales Price	Commission		Price Ranges	Commissions
Irons	790000	=B3*IF(B3<=$E$4,$F$3,IF(B3<=$E$5,$F$4,IF(B3<=$E$6,$F$5,IF(B3<=$E$7,$F$6,IF(B3<=$E$8,$F$7,IF(B3<=$E$9,$F$8,$F$9))))))		0	0.01
Forrest	889000	=B4*IF(B4<=$E$4,$F$3,IF(B4<=$E$5,$F$4,IF(B4<=$E$6,$F$5,IF(B4<=$E$7,$F$6,IF(B4<=$E$8,$F$7,IF(B4<=$E$9,$F$8,$F$9))))))		100000	0.02
Karim	923000	=B5*IF(B5<=$E$4,$F$3,IF(B5<=$E$5,$F$4,IF(B5<=$E$6,$F$5,IF(B5<=$E$7,$F$6,IF(B5<=$E$8,$F$7,IF(B5<=$E$9,$F$8,$F$9))))))		400000	0.025
Forrest	973000	=B6*IF(B6<=$E$4,$F$3,IF(B6<=$E$5,$F$4,IF(B6<=$E$6,$F$5,IF(B6<=$E$7,$F$6,IF(B6<=$E$8,$F$7,IF(B6<=$E$9,$F$8,$F$9))))))		800000	0.03
Enders	280000	=B7*IF(B7<=$E$4,$F$3,IF(B7<=$E$5,$F$4,IF(B7<=$E$6,$F$5,IF(B7<=$E$7,$F$6,IF(B7<=$E$8,$F$7,IF(B7<=$E$9,$F$8,$F$9))))))		1000000	0.035
Irons	339000	=B8*IF(B8<=$E$4,$F$3,IF(B8<=$E$5,$F$4,IF(B8<=$E$6,$F$5,IF(B8<=$E$7,$F$6,IF(B8<=$E$8,$F$7,IF(B8<=$E$9,$F$8,$F$9))))))		1500000	0.04
Qiu	485000	=B9*IF(B9<=$E$4,$F$3,IF(B9<=$E$5,$F$4,IF(B9<=$E$6,$F$5,IF(B9<=$E$7,$F$6,IF(B9<=$E$8,$F$7,IF(B9<=$E$9,$F$8,$F$9))))))		2000000	0.045
Irons	551000	=B10*IF(B10<=$E$4,$F$3,IF(B10<=$E$5,$F$4,IF(B10<=$E$6,$F$5,IF(B10<=$E$7,$F$6,IF(B10<=$E$8,$F$7,IF(B10<=$E$9,$F$8,$F$9))))))
Qiu	1240000	=B11*IF(B11<=$E$4,$F$3,IF(B11<=$E$5,$F$4,IF(B11<=$E$6,$F$5,IF(B11<=$E$7,$F$6,IF(B11<=$E$8,$F$7,IF(B11<=$E$9,$F$8,$F$9))))))
Qiu	991000	=B12*IF(B12<=$E$4,$F$3,IF(B12<=$E$5,$F$4,IF(B12<=$E$6,$F$5,IF(B12<=$E$7,$F$6,IF(B12<=$E$8,$F$7,IF(B12<=$E$9,$F$8,$F$9))))))
Enders	1277000	=B13*IF(B13<=$E$4,$F$3,IF(B13<=$E$5,$F$4,IF(B13<=$E$6,$F$5,IF(B13<=$E$7,$F$6,IF(B13<=$E$8,$F$7,IF(B13<=$E$9,$F$8,$F$9))))))
Irons	777000	=B14*IF(B14<=$E$4,$F$3,IF(B14<=$E$5,$F$4,IF(B14<=$E$6,$F$5,IF(B14<=$E$7,$F$6,IF(B14<=$E$8,$F$7,IF(B14<=$E$9,$F$8,$F$9))))))
Enders	262000	=B15*IF(B15<=$E$4,$F$3,IF(B15<=$E$5,$F$4,IF(B15<=$E$6,$F$5,IF(B15<=$E$7,$F$6,IF(B15<=$E$8,$F$7,IF(B15<=$E$9,$F$8,$F$9))))))
Karim	669000	=B16*IF(B16<=$E$4,$F$3,IF(B16<=$E$5,$F$4,IF(B16<=$E$6,$F$5,IF(B16<=$E$7,$F$6,IF(B16<=$E$8,$F$7,IF(B16<=$E$9,$F$8,$F$9))))))
Karim	700000	=B17*IF(B17<=$E$4,$F$3,IF(B17<=$E$5,$F$4,IF(B17<=$E$6,$F$5,IF(B17<=$E$7,$F$6,IF(B17<=$E$8,$F$7,IF(B17<=$E$9,$F$8,$F$9))))))
Forrest	1399000	=B18*IF(B18<=$E$4,$F$3,IF(B18<=$E$5,$F$4,IF(B18<=$E$6,$F$5,IF(B18<=$E$7,$F$6,IF(B18<=$E$8,$F$7,IF(B18<=$E$9,$F$8,$F$9))))))
	=SUM(B3:B18)	=SUM(C3:C18)
	Performance Table
	Associate	Properties Sold	Total Sales	Commission
	Enders	=COUNTIF($A$3:$A$18,B23)	=SUMIF($A$3:$B$18,B23,$B$3:$B$18)	=SUMIF($A$3:$C$18,B23,$C$3:$C$18)
	Forrest	=COUNTIF($A$3:$A$18,B24)	=SUMIF($A$3:$B$18,B24,$B$3:$B$18)	=SUMIF($A$3:$C$18,B24,$C$3:$C$18)
	Irons	=COUNTIF($A$3:$A$18,B25)	=SUMIF($A$3:$B$18,B25,$B$3:$B$18)	=SUMIF($A$3:$C$18,B25,$C$3:$C$18)
	Karim	=COUNTIF($A$3:$A$18,B26)	=SUMIF($A$3:$B$18,B26,$B$3:$B$18)	=SUMIF($A$3:$C$18,B26,$C$3:$C$18)
	Qiu	=COUNTIF($A$3:$A$18,B27)	=SUMIF($A$3:$B$18,B27,$B$3:$B$18)	=SUMIF($A$3:$C$18,B27,$C$3:$C$18)
			=SUM(D23:D27)	=SUM(E23:E27)
	Top Perfomer
	Total Sales	Associate	Properties Sold	Commission
	=MAX(D23:D27)	=INDEX(B23:D27,2,1)	=VLOOKUP(C32,B23:E27,2,0)	=VLOOKUP(C32,B23:E27,4,0)