Help me "dissect" this function (Excel 2002 [XP])

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike Webb - 05 Jul 2006 19:41 GMT

=DSUM(Membership,Membership!$C$49,C1:C3)+DSUM(Revenue,'Revenue
Projects'!$D$2,C1:C3)

I'm trying to adapt a budget worksheet from another non-profit for our use.
I don't know who the author is and I've had it a couple years, so trying to
go that route for help is a non-starter.

I don't understand why they have Membership twice; I guess one is for the
worksheet and the other is the column header, but I don't get it as $C$49
points to a a column label named "Revenue". and C1:C3 point to 2 blank cells
and one with the column label "2002". Somehow, the orginal formula was
pulling $10,000 frm this worksheet. I can't figure out how. In the 2nd
half of the function I don't have a worksheet named Revenue, but do ahve one
named Revenue Projects - but no column lable of Revenue. Anyway $D$2 is a
column label called "Monthly Giving" (don't know why I need that in this
formula), and C1:C3 are a blank line, column header, and some text. Now the
number I CAN pull frm MY formula - $600 - can be found in cell D4. In case
it's useful, cell A4 has trhe following text: "Monthly Giving".

I can provide much more info on the workbook, but I limited myself to the
immediate problem. (P.S. What do some formulas use $ signs in front of the
cell column and row ID's and others don't? Couldn't find it in the Help
menu.)

Signature

Mike Webb
Platte River Whooping Crane Maintenance Trust, Inc.
a 501 (c)(3) conservation non-profit organization

Reply to this Message

kassie - 05 Jul 2006 20:11 GMT

I think you will have a much clearer understanding if you hit help in Excel,
and type in DSUM. It not only explains the syntax, but also gives a very
good example of how to use inter alia DSUM.

As for the last part of your question, the $ sign is used to make a cell
reference absolute, as opposed to relative. Iow, if you enter the following
formula in B1 - =A1, and you copy it to C1, it becomes =B1. If you copy it
to B2, it becomes A2. However, $A1, if copied to C1, will still read =$A1.
If you copy this to B2, it will read =$A2.
=$A$1 will remain as =$A$1, no matter where you copy it to.
A$1, when copied to B1, will read =B$1, but if copied to B2, will read =A$1

> =DSUM(Membership,Membership!$C$49,C1:C3)+DSUM(Revenue,'Revenue
> Projects'!$D$2,C1:C3)
[quoted text clipped - 19 lines]
> cell column and row ID's and others don't? Couldn't find it in the Help
> menu.)

Reply to this Message

Mike Webb - 05 Jul 2006 20:39 GMT

Thanks for the explanation of the $ sign - it helps a lot.

However, I'm still unsure about DSUM.

I just enterred DSUM into Help and got the definition -
Database-Field-Criteria. Using this as the "decoder", the 1st "Membership"
would be the Database (worksheet), "Membership!$C$49" is the Field, and
"C1:C3" is the criteria - correct?
If the Field is the column used, then I don't get the "Membership" half.
That word is used in only one cell on the worksheet - A1. As I mentioned
below, $C$49 refers to a column label named "Revenue" (which is not used on
the original worksheet that has the cell with this formula). And the
criteria makes no sense when I look at what's in those cells. I tried
hitting "Example" in Help for DSUM, but their example:
=DSUM(A4:E10,"Profit",A1:F2) The total profit from apple trees with a height
between 10 and 16. (75)

doesn't help me.

Can someone give me a bit more to go on?

Mike

>I think you will have a much clearer understanding if you hit help in
>Excel,
[quoted text clipped - 44 lines]
>> cell column and row ID's and others don't? Couldn't find it in the Help
>> menu.)

Reply to this Message

kassie - 06 Jul 2006 19:56 GMT

Hi Mike

Glad I could help a litle bit. For help on the DSUM issue, Iwould really
need to examine your file. If you do not get any other responses to your
post, I would suggest you email me your file(s). I will then be in a much
better position to assist you. Mail me at kassie_kasselman@sjoemail.com, but
change the sjoe to hot

> Thanks for the explanation of the $ sign - it helps a lot.
>
[quoted text clipped - 67 lines]
> >> cell column and row ID's and others don't? Couldn't find it in the Help
> >> menu.)

Reply to this Message

Mike Webb - 06 Jul 2006 20:38 GMT

Wil do, thanks.

> Hi Mike
>
[quoted text clipped - 94 lines]
>> >> Help
>> >> menu.)

Reply to this Message

Rate this thread: