JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser .
Average pay wage per grade
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!!!
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
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>
Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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>
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
Threads
1,215,039
Messages
6,122,802
Members
449,095
Latest member
m_smith_solihull
