averageIF function not working, please help

[mpfiorv]

please help with the following #DIV/0! error..thank you

simple averageif function not working...what am i doing wrong?

[mpfiorv]

error1.jpg


here is error 1

[Glenn Kennedy]

On my PC and on my phone... I can't see your jpg. In any event it is easier with an Excel sheet. Can you attach a sheet (no confidential data) so that we can see what's going on?

[mpfiorv]

er1.jpger2.gif

can you see this at all?

[mpfiorv]

here is the other one:

er1.jpg

[mpfiorv]

error1.jpg

can ya see this one?

[mpfiorv]

can ya see this one?

[mpfiorv]

well, im trying to get an average salary using all the names, assuming column D is equal to five

[FDibbins]

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[mpfiorv]

ok, thanks for your help again...ill try this again..

[mpfiorv]

still not working....

[mpfiorv]

error1.jpg

this is another attempt

[mpfiorv]

error1.jpg

again................

[FDibbins]

You have been asked a few times to upload a WORKBOOK, not a picture of it. Even IF the pics come through, pictures are pretty much impossible to edit, and no-one wants to re-type your data for you

[daddylonglegs]

Which formula are you using? I'm assuming something like

=AVERAGEIF(D:D,5,F:F)

If that gives #DIV/0! error that means there is nothing to average. As you clearly have 5s in column D that might mean your column F is formatted as text. You could test that by using this formula which counts numbers

=COUNT(F:F)

What result do you get with that?

[mpfiorv]

Ok, here is a workbook...sorry for the confusion.

on the HR tab or worksheet, how would i use the AVERAGEIF function to find the average salary (cell B21) of employees with a rating of 5? Right now, im using the AVERAGEIFS function to find the average.

In addition, how would i use the SUMIF function to find the salaries of employees with a rating of 5 (cell B22)?
Right now, im using the SUMIFS function to find the salaries.

Remember for both function, the criteria is employees with a rating of 5...

please let me know if u do not understand the question...thank you

again thanks for your help

[FDibbins]

I dont see anything wrong with B21 or B22, what answer were you expecting?

Edit:
you could reference the "5" so you dont need to edit the forumla, and wrap it in IFERROR to error-trap...
=IFERROR(AVERAGEIFS(F6:F14,D6:D14,B20),"")

[mpfiorv]

heh....theres nothing wrong because im using the AVERAGEIS and SUMIFS functions.....my question...is how do i use the AVERAGEIF and SUMIF function to find the same answer to the questions .... are ya still confused ?? let me know

[mpfiorv]

Averageis = averageifs

[FDibbins]

To use xxxIF() version, instead of xxxIFs(), remove the "s", put the range to be averaged/summed as the last argument, instead if the 1st, and make sure you only have 1 criteria range and 1 criteria.

However, why would you want to change them? They are working fine, and there will be no difference to the calc or calc time, so I see np practical reason to change them