I have a table with the names of the workers in column A and their monthly salaries in columns B, C and D. What formula should I put in column E if I want to count the average wage of every worker independently, but only if it will be higher than $500?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
"AVERAGE" Formula
0
Answers
0
Selected Answer
Jatden
Firstly it's better not to type $ in number cell- just type the number but set the format to Currency (and adjust decimal places if needed).
In the revision to your file (attached) , I've done that and set (blue) cell F2 as a threshold value (so you could make it 0, 500, 550 or whatever). The formula for the average of salaries per worker but only above that threshold if (e.g. in F3) is:
=IFERROR(SUMIF(B3:D3,">"&$F$2)/COUNTIF(B3:D3,">"&$F$2),"-")SUMIF(B3:D3,">"&$F$2)COUNTIF is identical but counts the cells meeting the criteria. The average is the sum over the count as you will know.
If there are no matching values (so the COUNTIF is 0), you'll get a #DIV0 error so I've wrapped the formula with the IFERROR function, in which case the cell becomes the "-" (or whatever you choose) at the end of the formula.
Hope this makes sense / fixes your problem.
Discussion
Jayden, you didn't comment on this or select it. Did you get an advisory email about the Answer being posted?
John_Ru (rep: 6152) Sep 24, '21 at 11:42 am
Sorry, I was unable to comment for the last several days. Thanks a lot for your answer, it helped to solve my problem!
Jayden Smith (rep: 10) Sep 27, '21 at 3:40 pm
Jayden. Glad it helped and thanks for selecting my answer.
John_Ru (rep: 6152) Sep 27, '21 at 4:51 pm