Average pay wage per grade

[lastcor3]

I have to find the average pay wage per grade. For instance, M4 and P5 fall under the grade of 19. Therefore, I should calculate (M4+P5)/2. For grade 18 I should calculate (M4+P4)/2, and so on. For grades like Execs-E0 I should find the average of grade E0 because this is the only grade which falls under Execs-E0.

Thank you very much for your help!!!

<tbody>
</tbody>

G
M
T

<input id="SL_locer" title="Lock-in language" type="checkbox">

<select id="SL_lng_from" style="background: rgb(255, 255, 255) url("moz-extension://92a75f45-8be0-4181-b0a8-bfc3b5e100a8/content/img/util/select.png") no-repeat scroll 100% 0px;"><option value="auto">Detect language</option><option value="af">Afrikaans</option><option value="sq">Albanian</option><option value="ar">Arabic</option><option value="hy">Armenian</option><option value="az">Azerbaijani</option><option value="eu">Basque</option><option value="be">Belarusian</option><option value="bn">Bengali</option><option value="bs">Bosnian</option><option value="bg">Bulgarian</option><option value="ca">Catalan</option><option value="ceb">Cebuano</option><option value="ny">Chichewa</option><option value="zh-CN">Chinese (Simplified)</option><option value="zh-TW">Chinese (Traditional)</option><option value="hr">Croatian</option><option value="cs">Czech</option><option value="da">Danish</option><option value="nl">Dutch</option><option value="en">English</option><option value="eo">Esperanto</option><option value="et">Estonian</option><option value="tl">Filipino</option><option value="fi">Finnish</option><option value="fr">French</option><option value="gl">Galician</option><option value="ka">Georgian</option><option value="de">German</option><option value="el">Greek</option><option value="gu">Gujarati</option><option value="ht">Haitian Creole</option><option value="ha">Hausa</option><option value="iw">Hebrew</option><option value="hi">Hindi</option><option value="hmn">Hmong</option><option value="hu">Hungarian</option><option value="is">Icelandic</option><option value="ig">Igbo</option><option value="id">Indonesian</option><option value="ga">Irish</option><option value="it">Italian</option><option value="ja">Japanese</option><option value="jw">Javanese</option><option value="kn">Kannada</option><option value="kk">Kazakh</option><option value="km">Khmer</option><option value="ko">Korean</option><option value="lo">Lao</option><option value="la">Latin</option><option value="lv">Latvian</option><option value="lt">Lithuanian</option><option value="mk">Macedonian</option><option value="mg">Malagasy</option><option value="ms">Malay</option><option value="ml">Malayalam</option><option value="mt">Maltese</option><option value="mi">Maori</option><option value="mr">Marathi</option><option value="mn">Mongolian</option><option value="my">Myanmar (Burmese)</option><option value="ne">Nepali</option><option value="no">Norwegian</option><option value="fa">Persian</option><option value="pl">Polish</option><option value="pt">Portuguese</option><option value="pa">Punjabi</option><option value="ro">Romanian</option><option value="ru">Russian</option><option value="sr">Serbian</option><option value="st">Sesotho</option><option value="si">Sinhala</option><option value="sk">Slovak</option><option value="sl">Slovenian</option><option value="so">Somali</option><option value="es">Spanish</option><option value="su">Sundanese</option><option value="sw">Swahili</option><option value="sv">Swedish</option><option value="tg">Tajik</option><option value="ta">Tamil</option><option value="te">Telugu</option><option value="th">Thai</option><option value="tr">Turkish</option><option value="uk">Ukrainian</option><option value="ur">Urdu</option><option value="uz">Uzbek</option><option value="vi">Vietnamese</option><option value="cy">Welsh</option><option value="yi">Yiddish</option><option value="yo">Yoruba</option><option value="zu">Zulu</option></select>

<select id="SL_lng_to" style="background: rgb(255, 255, 255) url("moz-extension://92a75f45-8be0-4181-b0a8-bfc3b5e100a8/content/img/util/select.png") no-repeat scroll 100% 0px;"><option value="af">Afrikaans</option><option value="sq">Albanian</option><option value="ar">Arabic</option><option value="hy">Armenian</option><option value="az">Azerbaijani</option><option value="eu">Basque</option><option value="be">Belarusian</option><option value="bn">Bengali</option><option value="bs">Bosnian</option><option value="bg">Bulgarian</option><option value="ca">Catalan</option><option value="ceb">Cebuano</option><option value="ny">Chichewa</option><option value="zh-CN">Chinese (Simplified)</option><option value="zh-TW">Chinese (Traditional)</option><option value="hr">Croatian</option><option value="cs">Czech</option><option value="da">Danish</option><option value="nl">Dutch</option><option selected="selected" value="en">English</option><option value="eo">Esperanto</option><option value="et">Estonian</option><option value="tl">Filipino</option><option value="fi">Finnish</option><option value="fr">French</option><option value="gl">Galician</option><option value="ka">Georgian</option><option value="de">German</option><option value="el">Greek</option><option value="gu">Gujarati</option><option value="ht">Haitian Creole</option><option value="ha">Hausa</option><option value="iw">Hebrew</option><option value="hi">Hindi</option><option value="hmn">Hmong</option><option value="hu">Hungarian</option><option value="is">Icelandic</option><option value="ig">Igbo</option><option value="id">Indonesian</option><option value="ga">Irish</option><option value="it">Italian</option><option value="ja">Japanese</option><option value="jw">Javanese</option><option value="kn">Kannada</option><option value="kk">Kazakh</option><option value="km">Khmer</option><option value="ko">Korean</option><option value="lo">Lao</option><option value="la">Latin</option><option value="lv">Latvian</option><option value="lt">Lithuanian</option><option value="mk">Macedonian</option><option value="mg">Malagasy</option><option value="ms">Malay</option><option value="ml">Malayalam</option><option value="mt">Maltese</option><option value="mi">Maori</option><option value="mr">Marathi</option><option value="mn">Mongolian</option><option value="my">Myanmar (Burmese)</option><option value="ne">Nepali</option><option value="no">Norwegian</option><option value="fa">Persian</option><option value="pl">Polish</option><option value="pt">Portuguese</option><option value="pa">Punjabi</option><option value="ro">Romanian</option><option value="ru">Russian</option><option value="sr">Serbian</option><option value="st">Sesotho</option><option value="si">Sinhala</option><option value="sk">Slovak</option><option value="sl">Slovenian</option><option value="so">Somali</option><option value="es">Spanish</option><option value="su">Sundanese</option><option value="sw">Swahili</option><option value="sv">Swedish</option><option value="tg">Tajik</option><option value="ta">Tamil</option><option value="te">Telugu</option><option value="th">Thai</option><option value="tr">Turkish</option><option value="uk">Ukrainian</option><option value="ur">Urdu</option><option value="uz">Uzbek</option><option value="vi">Vietnamese</option><option value="cy">Welsh</option><option value="yi">Yiddish</option><option value="yo">Yoruba</option><option value="zu">Zulu</option></select>

Text-to-speech function is limited to 200 characters

<input id="SL_BBL_locer" checked="1" title="Show Translator's button 3 second(s)" type="checkbox">

Options : History : Feedback : Donate

Close

 

[oldbrewer]

name	GRADE	HAY	salary					TOT WAGE	TOT NUM	AVE
name1	P6	20	100				P6	1090	7	155.7143
name2	M5	20	105				M5	825	5	165	160.3571
name3	P5	19	110				P5	1460	8	182.5
name4	M4	19	115				M4	1800	10	180	181.25
name5	P6	19	120
name6	M4	19	125
name7	P6	20	130
name8	M5	20	135			HAY 20	159.5833
name9	P5	19	140			HAY 19	181.1111
name10	M4	19	145
name11	P5	19	150
name12	M4	19	155
name13	P6	20	160			NOTE THAT THE HAY AVES ARE OBTAINED BY ADDING
name14	M5	20	165			ALL THE P6 AND M5 SALARIES AND DIVIDING BY
name15	P6	19	170			NUMBER OF P6 AND M5
name16	M4	19	175
name17	P5	19	180			THIS IS CORRECT
name18	M4	19	185
name19	P6	20	190			AVERAGING THE AVERAGES IN THE TABLE ABOVE
name20	M5	20	195			GIVES INCORRECT ANSWERS
name21	P5	19	200
name22	M4	19	205
name23	P5	19	210
name24	M4	19	215
name25	P6	20	220
name26	M5	20	225
name27	P5	19	230
name28	M4	19	235
name29	P5	19	240
name30	M4	19	245

<colgroup><col span="8"><col><col span="5"></colgroup><tbody>
</tbody>

 

[oldbrewer]

name	GRADE	HAY	salary					TOT WAGE	TOT NUM	AVE
name1	P6	20	100				P6	1090	7	155.7143
name2	M5	20	105				M5	825	5	165	160.3571
name3	P5	19	110				P5	1460	8	182.5
name4	M4	19	115				M4	1800	10	180	181.25
name5	P6	19	120
name6	M4	19	125
name7	P6	20	130
name8	M5	20	135			HAY 20	159.5833		=(SUMPRODUCT(($B$2:$B$31="P6")*($D$2:$D$31))+SUMPRODUCT(($B$2:$B$31="M5")*($D$2:$D$31)))/(SUMPRODUCT(($B$2:$B$31="P6")*1)+SUMPRODUCT(($B$2:$B$31="M5")*1))
name9	P5	19	140			HAY 19	181.1111
name10	M4	19	145
name11	P5	19	150
name12	M4	19	155
name13	P6	20	160			NOTE THAT THE HAY AVES ARE OBTAINED BY ADDING
name14	M5	20	165			ALL THE P6 AND M5 SALARIES AND DIVIDING BY
name15	P6	19	170			NUMBER OF P6 AND M5
name16	M4	19	175
name17	P5	19	180			THIS IS CORRECT
name18	M4	19	185
name19	P6	20	190			AVERAGING THE AVERAGES IN THE TABLE ABOVE
name20	M5	20	195			GIVES INCORRECT ANSWERS
name21	P5	19	200
name22	M4	19	205
name23	P5	19	210
name24	M4	19	215
name25	P6	20	220			1090		=SUMPRODUCT(($B$2:$B$31=H2)*($D$2:$D$31))
name26	M5	20	225
name27	P5	19	230			7		=SUMPRODUCT(($B$2:$B$31=H2)*1)
name28	M4	19	235
name29	P5	19	240
name30	M4	19	245

<colgroup><col span="8"><col><col span="21"></colgroup><tbody>
</tbody>

 

[lastcor3]

name	GRADE	HAY	salary					TOT WAGE	TOT NUM	AVE
name1	P6	20	100				P6	1090	7	155.7143
name2	M5	20	105				M5	825	5	165	160.3571
name3	P5	19	110				P5	1460	8	182.5
name4	M4	19	115				M4	1800	10	180	181.25
name5	P6	19	120
name6	M4	19	125
name7	P6	20	130
name8	M5	20	135			HAY 20	159.5833		=(SUMPRODUCT(($B$2:$B$31="P6")*($D$2:$D$31))+SUMPRODUCT(($B$2:$B$31="M5")*($D$2:$D$31)))/(SUMPRODUCT(($B$2:$B$31="P6")*1)+SUMPRODUCT(($B$2:$B$31="M5")*1))
name9	P5	19	140			HAY 19	181.1111
name10	M4	19	145
name11	P5	19	150
name12	M4	19	155
name13	P6	20	160			NOTE THAT THE HAY AVES ARE OBTAINED BY ADDING
name14	M5	20	165			ALL THE P6 AND M5 SALARIES AND DIVIDING BY
name15	P6	19	170			NUMBER OF P6 AND M5
name16	M4	19	175
name17	P5	19	180			THIS IS CORRECT
name18	M4	19	185
name19	P6	20	190			AVERAGING THE AVERAGES IN THE TABLE ABOVE
name20	M5	20	195			GIVES INCORRECT ANSWERS
name21	P5	19	200
name22	M4	19	205
name23	P5	19	210
name24	M4	19	215
name25	P6	20	220			1090		=SUMPRODUCT(($B$2:$B$31=H2)*($D$2:$D$31))
name26	M5	20	225
name27	P5	19	230			7		=SUMPRODUCT(($B$2:$B$31=H2)*1)
name28	M4	19	235
name29	P5	19	240
name30	M4	19	245

<tbody>
</tbody>

Thank you for the reply! I first did it with bunch of nested IF statements, but at some point I wasn't able to nest more IF statements because they were so many. I have tried it with SUMPRODUCT but the same thing happened again. Is there are possible solution with less nested formulas?

I need to calculate the average pay wage per Hay grade within 3 columns at max.



G
M
T

<input id="SL_locer" title="Lock-in language" type="checkbox">

<select id="SL_lng_from" style="background: rgb(255, 255, 255) url("moz-extension://dc6f359a-9aed-4c07-98fc-7fc25dea5164/content/img/util/select.png") no-repeat scroll 100% 0px;"><option value="auto">Detect language</option><option value="af">Afrikaans</option><option value="sq">Albanian</option><option value="ar">Arabic</option><option value="hy">Armenian</option><option value="az">Azerbaijani</option><option value="eu">Basque</option><option value="be">Belarusian</option><option value="bn">Bengali</option><option value="bs">Bosnian</option><option value="bg">Bulgarian</option><option value="ca">Catalan</option><option value="ceb">Cebuano</option><option value="ny">Chichewa</option><option value="zh-CN">Chinese (Simplified)</option><option value="zh-TW">Chinese (Traditional)</option><option value="hr">Croatian</option><option value="cs">Czech</option><option value="da">Danish</option><option value="nl">Dutch</option><option value="en">English</option><option value="eo">Esperanto</option><option value="et">Estonian</option><option value="tl">Filipino</option><option value="fi">Finnish</option><option value="fr">French</option><option value="gl">Galician</option><option value="ka">Georgian</option><option value="de">German</option><option value="el">Greek</option><option value="gu">Gujarati</option><option value="ht">Haitian Creole</option><option value="ha">Hausa</option><option value="iw">Hebrew</option><option value="hi">Hindi</option><option value="hmn">Hmong</option><option value="hu">Hungarian</option><option value="is">Icelandic</option><option value="ig">Igbo</option><option value="id">Indonesian</option><option value="ga">Irish</option><option value="it">Italian</option><option value="ja">Japanese</option><option value="jw">Javanese</option><option value="kn">Kannada</option><option value="kk">Kazakh</option><option value="km">Khmer</option><option value="ko">Korean</option><option value="lo">Lao</option><option value="la">Latin</option><option value="lv">Latvian</option><option value="lt">Lithuanian</option><option value="mk">Macedonian</option><option value="mg">Malagasy</option><option value="ms">Malay</option><option value="ml">Malayalam</option><option value="mt">Maltese</option><option value="mi">Maori</option><option value="mr">Marathi</option><option value="mn">Mongolian</option><option value="my">Myanmar (Burmese)</option><option value="ne">Nepali</option><option value="no">Norwegian</option><option value="fa">Persian</option><option value="pl">Polish</option><option value="pt">Portuguese</option><option value="pa">Punjabi</option><option value="ro">Romanian</option><option value="ru">Russian</option><option value="sr">Serbian</option><option value="st">Sesotho</option><option value="si">Sinhala</option><option value="sk">Slovak</option><option value="sl">Slovenian</option><option value="so">Somali</option><option value="es">Spanish</option><option value="su">Sundanese</option><option value="sw">Swahili</option><option value="sv">Swedish</option><option value="tg">Tajik</option><option value="ta">Tamil</option><option value="te">Telugu</option><option value="th">Thai</option><option value="tr">Turkish</option><option value="uk">Ukrainian</option><option value="ur">Urdu</option><option value="uz">Uzbek</option><option value="vi">Vietnamese</option><option value="cy">Welsh</option><option value="yi">Yiddish</option><option value="yo">Yoruba</option><option value="zu">Zulu</option></select>

<select id="SL_lng_to" style="background: rgb(255, 255, 255) url("moz-extension://dc6f359a-9aed-4c07-98fc-7fc25dea5164/content/img/util/select.png") no-repeat scroll 100% 0px;"><option value="af">Afrikaans</option><option value="sq">Albanian</option><option value="ar">Arabic</option><option value="hy">Armenian</option><option value="az">Azerbaijani</option><option value="eu">Basque</option><option value="be">Belarusian</option><option value="bn">Bengali</option><option value="bs">Bosnian</option><option value="bg">Bulgarian</option><option value="ca">Catalan</option><option value="ceb">Cebuano</option><option value="ny">Chichewa</option><option value="zh-CN">Chinese (Simplified)</option><option value="zh-TW">Chinese (Traditional)</option><option value="hr">Croatian</option><option value="cs">Czech</option><option value="da">Danish</option><option value="nl">Dutch</option><option selected="selected" value="en">English</option><option value="eo">Esperanto</option><option value="et">Estonian</option><option value="tl">Filipino</option><option value="fi">Finnish</option><option value="fr">French</option><option value="gl">Galician</option><option value="ka">Georgian</option><option value="de">German</option><option value="el">Greek</option><option value="gu">Gujarati</option><option value="ht">Haitian Creole</option><option value="ha">Hausa</option><option value="iw">Hebrew</option><option value="hi">Hindi</option><option value="hmn">Hmong</option><option value="hu">Hungarian</option><option value="is">Icelandic</option><option value="ig">Igbo</option><option value="id">Indonesian</option><option value="ga">Irish</option><option value="it">Italian</option><option value="ja">Japanese</option><option value="jw">Javanese</option><option value="kn">Kannada</option><option value="kk">Kazakh</option><option value="km">Khmer</option><option value="ko">Korean</option><option value="lo">Lao</option><option value="la">Latin</option><option value="lv">Latvian</option><option value="lt">Lithuanian</option><option value="mk">Macedonian</option><option value="mg">Malagasy</option><option value="ms">Malay</option><option value="ml">Malayalam</option><option value="mt">Maltese</option><option value="mi">Maori</option><option value="mr">Marathi</option><option value="mn">Mongolian</option><option value="my">Myanmar (Burmese)</option><option value="ne">Nepali</option><option value="no">Norwegian</option><option value="fa">Persian</option><option value="pl">Polish</option><option value="pt">Portuguese</option><option value="pa">Punjabi</option><option value="ro">Romanian</option><option value="ru">Russian</option><option value="sr">Serbian</option><option value="st">Sesotho</option><option value="si">Sinhala</option><option value="sk">Slovak</option><option value="sl">Slovenian</option><option value="so">Somali</option><option value="es">Spanish</option><option value="su">Sundanese</option><option value="sw">Swahili</option><option value="sv">Swedish</option><option value="tg">Tajik</option><option value="ta">Tamil</option><option value="te">Telugu</option><option value="th">Thai</option><option value="tr">Turkish</option><option value="uk">Ukrainian</option><option value="ur">Urdu</option><option value="uz">Uzbek</option><option value="vi">Vietnamese</option><option value="cy">Welsh</option><option value="yi">Yiddish</option><option value="yo">Yoruba</option><option value="zu">Zulu</option></select>

Text-to-speech function is limited to 200 characters

<input id="SL_BBL_locer" checked="1" title="Show Translator's button 3 second(s)" type="checkbox">

Options : History : Feedback : Donate

Close