My formula contains an error, I cant work it out

[scudo]

Formula =IFERROR(averageif(e19+h19+K19+n19+q19+t19+w19+z19+Ai19+al19,">20")*12,"")

If error to stop error message
adding cells if more than "20" is in the cell
and dividing the total by 12
So that I get an average of the 12 entries

I get a message that there is an error but cant work out what it is.

[mikeTRON]

Average if needs a range, and e19+h19 is not a range.

Can you post a sample workbook with the expected outcome and I can possibly help?

[Sam Capricci]

Perhaps you should try =if((sum(e19,h19,k19,n19,q19,t19,w19,z19,ai19,al19)/10)>"20")*12,"")

[scudo]

Workbook attached:
Tab: Sales.
Row 19 Formula for: =IFERROR(averageif(e19+h19+K19+n19+q19+t19+w19+z19+Ai19+al19,">20")*12,""
Answer in cell AB38 which I am trying to make as an average of the above cells.

PO Sales 2015 - Dunferm..xlsx

[mikeTRON]

What exact number is the output you are looking for?

Is this some sort of homework?

[scudo]

I wish at 65 I was still doing homework :-), No I am doing it for my wife so she can monitor her sales and staff at work.
I have attached a better copy as the previous one I am still building as she is moving branch and I need to change a few things.

The figure I am looking for is an average of her sales to appointments as a % so that a forecast can be made for the year on likely sales.
So in AB38 that would be an average of the %s in cells e19+h19+K19+n19+q19+t19+w19+z19+Ai19+al19, that average % would then be used to estimate sales for the year in AA39,
As it currently stands she has to put her average % rate in manually and I am trying to automate it based on a 12 month period as the year progresses.

PO Sales 2015 - 4-5 - Copy.xlsx

[Sam Capricci]

I think this will work for you instead…
=IF(((E19+H19+K19+N19+Q19+T19+W19+Z19+AI19+AL19)/10)>20,((E19+H19+K19+N19+Q19+T19+W19+Z19+AI19+AL19)/10)*12,"")

tested it and it works.

[scudo]

When I copy/paste your formula in to a cell I get #VALUE!

[FDibbins]

Where does the >20 come in?

[Sam Capricci]

I think that is because you have a lot of blanks in your cells. I tested it on my made up data.
try this instead…
=IFERROR(IF(((E19+H19+K19+N19+Q19+T19+W19+Z19+AI19+AL19)/10)>20,((E19+H19+K19+N19+Q19+T19+W19+Z19+AI19+AL19)/10)*12,""),"")

[scudo]

Earlier post:

Where does the >20 come in?

That was not to include if the figure was less than 20%, at least thats what I was trying to do.

Sambo Kid, that formula returns a blank cell with no error message.

[Sam Capricci]

I'm running out of options because I don't know what you want it to return.
so here is one with the average formula…
=IF((AVERAGE(E19,H19,K19,N19,Q19,T19,W19,Z19,AI19,AL19)>20),AVERAGE(E19,H19,K19,N19,Q19,T19,W19,Z19,AI19,AL19)*12,"")

[scudo]

My apologies as maybe its my explanation thats vague, let me try without using formulas.

In cell E19 i have a % (appointments v sales) for that month, as the year progresses I would have a % for each month, I then want to average those %s as the year progresses, so the % figure in cell AB38 would adjust as the year progresses. Then I would use this % figure to forecast the sales in cell AA39.

[Sam Capricci]

So what's the matter with
=AVERAGE(E19,H19,K19,N19,Q19,T19,W19,Z19,AI19,AL19)?

[teylyn]

Hello,

this would be easier to troubleshoot if we could see the workbook or at least a screenshot.

As far as I can see, the problem is two-fold:

- the data you want to average is not in a contiguous range
- the comparison with 20 does not return the correct results because the numbers are percentages and 20% is 0.2 - not 20

You probably have a row somewhere with labels for each column. You can leverage that to use AverageIfs with multiple conditions. In the screenshot, the labels are in row 1, the percentages in row 2. The first condition ensures that only the values in columns E, H, K and N are included. The second condition ensures that only values greater than 20% are included.

See if you can apply that to your situation.

=AVERAGEIFS(E2:N2,E1:N1,"percentage",E2:N2,">0.2")

2015-05-05_11-33-56.png

[scudo]

Its late so I will see what happens tomorrow but for some reason your formulas are not working, could it be something in cell formatting?
I will check tomorrow and insert your formula and if it still doesnt work will attach spreadsheet with formula inserted.

[scudo]

teylyn I will look at that tomorrow....eyes /brain getting tired now, I will post back.

[teylyn]

Yep. You'll be better when rested.

[SPARKY347]

I just made a new sheet and had all the cells line up from each month then using the sumif/ countif formals to spit out an average, then just had your sales sheet cell equal the final avarage

[scudo]

Ok let me try another way to explain:

In the cells that are highlighted in grey with the numbers 100, 60, 75
Those figures are % on the appointments to sales ratio so 60 would be a 60% conversion rate, ie 5 appointments converted to 3 sales = 60%.

So now what I want to do is add all the grey %s for the 12 months and get an average of the %s the answer in cell AB38.

I will then use that % figure to estimate actual sales for the year as a forecast.
Using the above figures 100,60,75 then the average % conversion rate with 3 months gone would be 78% (cell AB38, crrently showing 65)
I would then use that figure 78 as a % to estimate sales for the year so I would take say 78% of whatever the total for appointments is.

columns.JPG

year.JPG

[scudo]

Maybe this is better explanation.

=SUM(E19,H19,K19,N19,Q19,T19,W19,Z19,AC19,AF19,AI19,AL19)
But do not add up blank cells
Then divide by the number of cells with an entry in them.

[scudo]

double post

[teylyn]

Hi, I take it that the 20% threshold is no longer a topic?

You can use AverageIF, which will give you an average of all cells that contain a value in the columns with "%" in row 3

=AVERAGEIF(C3:W3,"%",C19:W19)

Tip: Avoid merged cells. You have merged cells across rows 3 and 4. It makes it difficult to determine which cell holds the value. The same look can be achieved with non-merged cells by increasing the row height. Merged cells can wreak havoc with formulas and other functionality and are best avoided.

[scudo]

Merged cells can wreak havoc

I wasnt aware of that so will try to eliminate for formulas.

I can leave the 20% threshold out as the more I think about it the more it seems unecessary.

OK the formula nearly works but gives the wrong answer as it is adding the 1 in column O19 (under Aug) so therefore dividing by 4 instead of three months.

form.JPG

[teylyn]

What's your result? Mine calculates correctly.


2015-05-06_8-27-36.png

[scudo]

My answer is 58.75 it appears to be picking the 1 up under Aug.
I have changed the row from 19 to 18 when I took the merged cells out.

Capture.JPG

[teylyn]

That 1 in August is not the cause. If it were, the result would be 59, not 58.75.

The only way to get to 58.75 is if one of the % columns contains a 0.

Looks like you are using formulas to calculate the percentage and format away zero values, but they are still in the cells. If you use formulas to calculate the percentage column, return a blank instead of a zero to have it ignored in the average.

[scudo]

Yes I am using eg =IFERROR(G5/F5*(100),"") to calculate the formulas and I have unchecked `show a zero` in advanced options.
I will go back to it now in view of what you have said and see if I can correct it.
I may need help at some point but will try my best.
thanks

[scudo]

return a blank instead of a zero to have it ignored in the average.

Ok I tried and failed, could you advise on how to alter to return a blank.

I will mark this thread as solved as I am going away for 3 weeks and will have limited internet access so will get back on the trail when I return.

[teylyn]

Turn on Zeros and check. One cell contains a Zero.

[scudo]

Got it sorted now, I was working on the columns and it should have been the cell I wanted the answer in which gave me the average.

Rightly or wrongly but this worked:
=IFERROR(AVERAGEIFS(C18:AL18,C3:AL3,"%",C18:AL18,">0"),"")

Many thanks all for helping.