I uploaded the workbook.
1. I need to calculate the average prices when quantity in stock is at 20 units and when price is at least $10
=AVERAGEIFS(E12:E63;B12:B63;"20";E12:E63;">=10")
I used the above formula and got an error : #DIV/!0
Please help
2. I also need to calculate the number 32 oz. Items that has a price of at least $7
=COUNTIFS(F12:F63;"32 oz";E12:E63;">=7")
I used the above formula and got 0 which is incorrect
Please assist with both formulas
1. You do not have any data with a quantity of exactly 20 and a price >=10. So it divides by zero and produces an error.
2. Change "32 oz" to "32 oz."
For the average, there are no entries that meet both conditions. There are a couple of cells in column B that =20 but none of the corresponding cells in column E are >=10.
For the count, you need to add a period after the "oz":
=COUNTIFS(F12:F63;"32 oz.";E12:E63;">=7")
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thanks a lot, to get the error off what would i have to do since it doesn't meet the condition for average?
This will return a blank instead of any errors...
=IFERROR(AVERAGEIFS(E12:E63;B12:B63;"20";E12:E63;">=10");"")
Thanks everyone.
You're welcome!
You're welcome! Thanks for the rep, and thanks for marking your thread Solved!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks