Microsoft Office 2007 professional (aca...

i am trying to fix a sheets subtotals and

continuously getting the "too many arguments

for this function" error.Help required with the following formula:

=

AVERAGE(L10:L16,L18:L24,L26:L32,L34:L40,

L42:L48,L50:L56) -

COUNTIF(D10:D16,D18:D24,D26:D32,D34:D4

0,D42:D48,D50:D56,"=0"))

I think it's your COUNTIF function:

=COUNTIF(RangeOfThingsToBeCounted,CriteriaToBeMatched)Example:

=COUNTIF(A1:A5,"Hello")

Are you perhaps looking for =COUNTA

=COUNTA(Range1,Range2,Range3... through to Range30)MIKE

actually the "D" cells represent the calls offered values, where

as the "L" cells values are the "Avg. Abandoned Wait Time"

values.I am trying to calculate monthly "avg", where it should not

calculate the days when i had "0" calls.the actual formula was:

=IF(COUNT(L10:L16,L18:L24,L26:L32,L34:L40,L42:L48,L50:L

56)=0,"0",AVERAGE(L10:L16,L18:L24,L26:L32,L34:L40,L42:L

48,L50:L56))

which is still calculating some of the "0" values in-between

these cells.Now i am trying to edit the formula: =SUM(L42:L48) /

(COUNT(L42:L48) - COUNTIF(D42:D48,"=0"))which works beautifully on the weekly totals but not on

monthly and give error "too many arguments for this function".Will really appreciate if you can help me on this as i am stuck

with this report for the last 4 hours trying to figure out a

formula.

btw... just so you know the "counta" gives me a

"##############" error when applied.

I don't really know what's giving you the error that you're getting, mainly because I don't get that error. However, regardless of what errors we're getting, the formula you posted won't work for 2 reasons:

1 - Your formula has an extra parenthesis at the end

2 - COUNTIF does not work with non-contiguous cells.

If what you are trying to do is find the Average of all the values in the various ranges in column L and then subtract the number of times there is a 0 in the various ranges in Column D, then try this,

Note the

{ }around the array of ranges inside the INDIRECT function.=AVERAGE(L10:L16,L18:L24,L26:L32,L34:L40,L42:L48,L50:L56) - SUM(COUNTIF(INDIRECT({"D10:D16","D18:D24","D26:D32","D34:D40","D42:D48","D50:D56"}),"=0"))

Disregard Response # 4. I was working on it while you were re-explaining what you are trying to do. However, using the same technique, try something like this:

=SUM(L42:L48, and all the rest of your L ranges) / SUM(COUNTIF(INDIRECT({"D10:D16","D18:D24","D26:D32","D34:D40","D42:D48","D50:D56"}),"<>0"))

nah.. still getting the "#####" error. checked for formats as

well and tried various but it doesnt change. Is there any way i

can send you the excel sheet so that you can please look it

up?i think the first step should be to first have the excel identify

the cells from "D" with "non-zero" values, then have it

calculate the average from cells in column "N". please advice

further(grrr.. i wish we could take avg. of avg. that should have made

my task a lot easier :( )

Mike: it does bring up some values but the result is not correct.

is there any way i can forward you the spread sheet for your

better understanding?

Did you try the formula I suggest in Response # 5? COUNTIF won't work with non-contiguous ranges, but

SUM(COUNTIF(INDIRECT({"A", "List", "Of", "Ranges"}),"<>0"))will.

Not at all sure about this,

but it seems it's the zeros that are causing the problems.

So would this work:=SUMIF(L10:L56,>0,l10:l56) / COUNTIF(L10:L56,>0)

EDIT:

If should probably read:

=SUMIF(L10:L56,>0,L10:L56) /

COUNTif(D10:D56,>0)

MIKE

yes i did.. =SUM(L10:L16,L18:L24,L26:L32,L34:L40,L42:L48,L50:L56)/

SUM(COUNTIF(INDIRECT({"D10:D16","D18:D24","D26:D32","D3

4:D40","D42:D48","D50:D56"}),"<>0"))the results are off by 54 seconds which is not right. Please

check the proposed suggestion in reply 6 from me

re: "Please check the proposed suggestion in reply 6 from me"Check it against what?

re:

have it calculate the average from cells in column "N"Where does Column N entered into any of this? Response 6 is the only time you've mentioned N.

re:

have excel identify the cells from "D" with "non-zero" valuesIdentify in what way?

If you want to count them, use my suggestion:

SUM(COUNTIF(INDIRECT({"D10:D16","D18:D24","D26:D32","etc"}),"<>0"))

sorry.. type.. its column "L" instead of n

Hi, At the risk of jumping in to a lot of responses ....

1. To calculate the average, as both the daily Avg. Abandoned Wait Time and the daily number of calls is different, you need to calculate the

TotalAbandoned Wait Time per day.2. Knowing the total Abandoned Wait Time each day, the total Abandoned Wait Time for

alldays can be calculated.3. The total Abandoned Wait Time for all days is divided by the total number of calls for all days, to give the final average.

Do this:

In column M, starting at M10 enter this formula:=D10*L10For days with no calls the result will be zero. For days with calls the result will be total Abandoned Wait Time on that day. Drag the formula down to row 56.

Cell M56 will contain=D56*L56In any empty cell enter this formula:

=SUM(M10:M56)/SUM(D10:D56)This should be the average for all calls.

Regards

DerbyDad03: you probably have the solution, but there is some mistake due to

which the result is incorrect.

Humar: i have the individual values for each day of the month pulled of

a cisco report and i also have the perfect weekly values

through: =SUM(L42:L48) /

(COUNT(L42:L48) - COUNTIF(D42:D48,"=0")) formula where it

matches the presented call column in "D" cells and if there is

a "0" value, ignores it while taking average in the weekly totals

in column "L".The problem is that when i try to take the monthly average

this way, the formula is correct, but i get the "too many arguments" error.the formula with the nest option by DerbyDad03 seems to the

one but its not calculating the correct results.Please advice.

is there any way i can post the file on this forum or at least put a

screen shot for someone to understand the exact issue.

You can't post the file, but you can post a screen shot. Members who have Images On can see the image.

You can also post the screen shot at any of the many photo sites and then post the link here.

I also, as requested, sent you an email address via PM. Please do not share the email address with anyone.

Hi, I was giving you a way of doing this without using AVERAGE() or COUNTIF()

No need for long or complicated formulas!

Give it a try.

Regards

=SUM(AbdnTime)/(COUNTA(NumCalls)-SUM(COUNTIF(ZeroCalls,"=0"))) Where:

AbdnTime is a Named Range that Refers To your L ranges:

L10:L16, L18:L24, etc.

NumCalls is a Named Range that Refers To your D ranges:

D10:D16, D18:D24, etc.

ZeroCalls is a Named Range that Refers To:

=INDIRECT({"D10:D16","D18:D24","D26:D32","D34:D40","D42:D48","D50:D56"})

I've sent the workbook back to you via email with some "comments". Look at the Named Ranges to see what I did.

Thank you guys.. i really appreicate all your help. The formula that worked is:

=SUM(L42:L48,L34:L40,L26:L32,L18:L24,L11:L16,L50:L56)/(C

OUNT(L42:L48,L34:L40,L26:L32,L18:L24,L11:L16,L50:L56)-

(COUNTIF(D10:D16,"=0")+(COUNTIF(D18:D24,"=0"))+(COUN

TIF(D26:D32,"=0"))+(COUNTIF(D34:D40,"=0"))+(COUNTIF(D4

2:D48,"=0"))+(COUNTIF(D50:D56,"=0"))))cheers mates.. and if you need any help with formulas, let me

know.

Did you look at the solution I offered in the workbook I sent back to you last night? You've basically done the same thing I did, except that I created Named Ranges which can now be used in other formula in your workbook. My suggestion uses 1 COUNTIF instead of 6.

I'd rather type/edit a Named Range reference than use all of the individual ranges the way you have.

Oh well, to each his own...

Hi, Glad to see that you got a formula to work.

Regarding the math - you appear to be using the average wait time per abandoned call in you calculation, but you have not included the number of abandoned calls in the calculation.

If the average wait time per abandoned call on one day was 10 seconds, with 1 call abandoned, then that day's total abandoned call time was 10 seconds. If there were three abandoned calls that day then the abandoned call time would be 30 seconds.

So, to calculate your average abandoned call wait time per day that calls were offered, you need to calculate each day's total abandoned call wait time first.

Regards

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History