too many arguments error

Microsoft Office 2007 professional (aca...
March 29, 2010 at 11:02:43
Specs: Windows XP
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"))


See More: too many arguments error

Report •

#1
March 29, 2010 at 11:43:46
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

http://www.skeptic.com/


Report •

#2
March 29, 2010 at 12:03:45
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.


Report •

#3
March 29, 2010 at 12:05:18
btw... just so you know the "counta" gives me a
"##############" error when applied.

Report •

Related Solutions

#4
March 29, 2010 at 12:14:58
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"))



Report •

#5
March 29, 2010 at 12:24:05
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"))


Report •

#6
March 29, 2010 at 12:28:17
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 :( )


Report •

#7
March 29, 2010 at 12:42:00
Mike:

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


Report •

#8
March 29, 2010 at 12:47:41
is there any way i can forward you the spread sheet for your
better understanding?

Report •

#9
March 29, 2010 at 12:53:34
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.

Report •

#10
March 29, 2010 at 12:56:10
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

http://www.skeptic.com/


Report •

#11
March 29, 2010 at 12:58:53
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


Report •

#12
March 29, 2010 at 13:49:09
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" values

Identify in what way?

If you want to count them, use my suggestion:

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


Report •

#13
March 29, 2010 at 14:32:11
sorry.. type.. its column "L" instead of n

Report •

#14
March 29, 2010 at 14:50:13
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 Total Abandoned Wait Time per day.

2. Knowing the total Abandoned Wait Time each day, the total Abandoned Wait Time for all days 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*L10
For 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*L56

In any empty cell enter this formula:

=SUM(M10:M56)/SUM(D10:D56)

This should be the average for all calls.

Regards


Report •

#15
March 29, 2010 at 15:24:02
DerbyDad03:

you probably have the solution, but there is some mistake due to
which the result is incorrect.


Report •

#16
March 29, 2010 at 15:31:40
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.


Report •

#17
March 29, 2010 at 15:35:51
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.

Report •

#18
March 29, 2010 at 15:46:26
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.


Report •

#19
March 29, 2010 at 16:05:41
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


Report •

#20
March 29, 2010 at 18:23:18
=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.


Report •

#21
March 30, 2010 at 10:05:28
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.


Report •

#22
March 30, 2010 at 14:17:45
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...


Report •

#23
March 31, 2010 at 07:58:38
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


Report •

Ask Question