Positive and Negative Calculations

[Nocean]

Hi,
I am having a problem with a sheet formula which will not doubt have a simple solution and hope someone will be able to assist me in automating parts! I do not want any VBA or Macros!

My sheet records via manual entry the number of persons within each membership group at a given period and is entered in Col/Cell B4, B5, B6 etc down to the end. (I have 550 groups currently).
The next four columns C4, D4, E4, F4 are the quarterly reviews of membership,( I.E C4 is Qtr 1, D4 is Qtr 2 etc). These columns are completed manually and can vary up and down. For example, if :

B4 starts with 20 members,
Qtr 1 (C4) maybe 20, (no change to the original membership therefore zero "0" )
Qtr 2 (D4) may drop by 20 members resulting in no members, (a change to the original membership "-20" )
Qtr 3 (E4) might gain 20 members (a change to the original membership to zero "0" )
Qtr 4 (F4) may lose 10 members, (a change to original membership "-10" )

This would give me a -10 membership loss in the year to that which I started with.
Equally the figures could be all negative (I hope not) which I would require a minus 20 to be shown (-20) in the total column K4.
Alternatively, if I had +30 members each quarter, I would need +10 to be shown against the original 20 memebrs and not 30+30+30+30 as this would be incorrect figure.

I wish to find the net total membership over the year compared to the starting membership which will result in either zero, positive or negative numeric.

I cannot seem to get negatives numerics to add correctly in Excel or be able to compare or similar against my original starting figure.

Any help, suggestions or alternatives to resolving this would be highly appreciated and I hope the above is understandable.

Thanks

[Gunther Maplethorpe]

A good start would be to post your workbook (or at least a representative copy), to which helpers can then enter the correct formulae.

[Richard Buttrey]

Hi,

What do you mean by Net total membership for the year?
From your description it appears that you only need to count the Quarter 4 column hence K4 would be

=B4-F4
assuming F4 contains the count of +10 members

If this is not what you want upload an actual workbook so that we can see your request in context.

Regards

[Nocean]

Hi,

What do you mean by Net total membership for the year?
From your description it appears that you only need to count the Quarter 4 column hence K4 would be

=B4-F4
assuming F4 contains the count of +10 members

If this is not what you want upload an actual workbook so that we can see your request in context.

Regards

Thanks for your reply, but I am trying to calculate the total change over the year rather than from the final quarter. If I lost members in say Qtr 2 against the starting figure but all other quarters are no change to the original, the total using Qtr 4 would not reflect the loss earlier in the year.

I have uploaded my sheet for which Col A to K are the areas I would like to solve and have filed in a couple of membership senario's. I have tried various formula as you will see below the main body and in Col K.

The rest of the sheet is currently out of sync and will be rectified upon resolving my query.

Perhaps I am not thinking outside the box enough , so any suggestions would be appreciated.

BTW, I did not receive an email notification of your responses, is this normal or do I need to do something?

Thanks

[Richard Buttrey]

Hi

I'm still not clear what you're trying to achieve. Are you wanting calculated numbers in column K, and if so what numbers do you expect.

With the existing formulae in Col K I don't see why you need to complicate them by using an INDIRECT() function. A straightforward SUM(G4:J4) does the same.

If cols C:D show the number of members at the end of each Qtr your existing formulae in G:J are effectively calculating the year to date movement, i.e. the -10 in I4 is the reduction from the start of 20 to the 10 members at the end of Qtr 3 in E4.

Hence the change in membership at the end of the year is the column J number

Or are you really saying that you want column K to show whatever the position is as you move through the year. i.e. at the end of Qtr1 when you don't have any entries yet in cols D:F K 4 should show zero? In which case perhaps the K4 formula you are looking for is

=INDEX(G4:J4,1,COUNTA(C4:F4))

HTH

[Nocean]

Thank you,

Your formula for my Col K seems to do what I want, fantastic.

I appreciate your assistance, trying to locate how to mark this "Solved" now.