Formula

[texansgal]

I am trying ot put in a formula. I have a two columns with Revenue listed.
One is the Estimate, one is the actual. If there is not an amount in the
Actual amount column, I added the cell that was in the Estimate cell.

I color coded the cell because I am breakingit out by Sales Person. I have 3
Sales People. I went in and did the SUM and went into each cell that is
colored coded to each person. I am getting an error.

Please help.

Thanks,
Vanessa

 

[OssieMac]

Not enough information to more than guess at what you might be doing wrong.
How are you going into SUM? Are you clicking on the sum icon? If not, try
using it.

Your reply suggests that you are summing non adjacent cells. Is this
correct? If so, after selecting the first cell or adjacent cells in a range
to sum, are you holding the Ctrl key while you select the remaining cells?

If the above does not help then when you get your error, highlight the
formula in the formula bar and copy and paste it into a reply here. Also what
is in the cells to be summed?

Regards,

OssieMac

 

[ShaneDevenshire]

Give us a sample showing some value and the results you are getting and the
results you want. For example:

A B C
1 Actuals Estimate Calculations
2 123 432 =(B2-A2)/B2
3 221 =IF(A3="",0,(B3-A3)/B3)

I get xxx in C2 but I want yyy, what formula do I need?

 

[texansgal]

Here is my formula:

=SUM(R8,R9,R13,R14,R17,R18,R26,R30,R31,R32,R33,R35,R40,R41,R45,R49,R50,R51,R52,R58,R60,R61,R67,R68,R69,R70,R71,R75,R83,R87,R2,R93,R97,R98,R108,R111,R116,R117,R119,R124,R132,R133,Q143,R145,Q155,Q156,Q157,Q159,Q162,Q166,Q172,Q175)

I clicked in the cell where I want the formula to be. I put it "=SUM" and
then clicked on each cell that I wanted to using while holding down "ctrl"...

Please tell me what I am doing wrong. I even tried without the "SUM" and
just the "=".

Thank you!

 

[ShaneDevenshire]

If we knew more we might be able to simplify the formula. However, if you
are doing exactly what you have written then what you need to do is type
=SUM( not =SUM and then start clicking on each of the cells you want to
include while holding down the Ctrl key.

 

[ShaneDevenshire]

Hi Texansgal,

I missed another point - if you are using Excel 2003 the maximum number of
arguments for any function is 30. You can reduce the number in your sample
formula if you replace references such as:

=SUM(R8,R9,R13,R14,R17,R18,R26,R30,R31,R32,R33,R35,R40,R41,

with

=SUM(R8:R9,R13:R14,R17:R18,R26,R30:R33

and so on. In other words R30,R31,R32,R33 counts at 4 arguments but R30:R33
counts as 1.

If this still doesn't get you under the 30 argument limit you can create
intermediate formulas, for example two formulas with 30 arguments and a third
formula that sums those two.

Alternatively you can use Excel 2007 it supports 256 arguments.

 

[texansgal]

OK... That is what it is then. I have too many arguments. How can I create an
intermediate formula? I am sorry... I am semi-new at formulas.

 

[Jon Peltier]

I think the OP had the parenthesis. The problem is that Excel limits
functions to 30 arguments within the parentheses.

However, if it's a sum you need, you don't need to use SUM(). You can use

=R8+R9+R13+R14+R17+R18+R26+R30+R31+R32+R33+R35+R40+R41+R45+R49+R50+R51+R52+R58+R60+R61+R67+R68+R69+R70+R71+R75+R83+R87+R2+R93+R97+R98+R108+R111+R116+R117+R119+R124+R132+R133+Q143+R145+Q155+Q156+Q157+Q159+Q162+Q166+Q172+Q175

- Jon

 

[Jon Peltier]

As I posted in another strand of this thread:

=R8+R9+R13+R14+R17+R18+R26+R30+R31+R32+R33+R35+R40+R41+R45+R49+R50+R51+R52+R58+R60+R61+R67+R68+R69+R70+R71+R75+R83+R87+R2+R93+R97+R98+R108+R111+R116+R117+R119+R124+R132+R133+Q143+R145+Q155+Q156+Q157+Q159+Q162+Q166+Q172+Q175

I think now the limit is 1024 characters.

- Jon