Excel anyone?

[MickLL]

Another tester for you.

Is anyone an excel expert?

What I want to do is to add a column of numbers but exclude numbers that are in a certain colour font.

Thus say I want to add 1,2 and 3. And the answer is 6. Then say I alter the colour of the '1' to red (or any other colour) I want the '1' to be excluded from the addition and have the answer '5' returned.

Can it be done?

MickLL

[Scphoto]

I found this......

Color Change And Calculation

Excel normally calculates the formula in a cell when a cell upon which that formula depends changes. For example, the formula =SUM(A1:A10) is recalculated when any cell in A1:A10 is changed. However, Excel does not consider changing a cell's color to be significant to calculation, and therefore will not necessarily recalculate a formula when a cell color is changed. Later on this page, we will see a function named CountColor that counts the number of cells in a range that have a specific color index. If you change the color of a cell in the range that is passed to CountColor, Excel will not recalculate the CountColor function and, therefore, the result of CountColor may not agree with the actual colors on the worksheet until a recalculation occurs. The relevant functions use Application.Volatile True to force them to be recalculated when any calculation is done, but this is still insufficient. Simply changing a cell color does not cause a calculation, so the function is not recalculated, even with Application.Volatile True.

From a Excel source i've used before (Cpearson.com)

[dangie]

Excel normally calculates the formula in a cell when a cell upon which that formula depends changes. For example, the formula =SUM(A1:A10) is recalculated when any cell in A1:A10 is changed. However, Excel does not consider changing a cell's color to be significant to calculation, and therefore will not necessarily recalculate a formula when a cell color is changed. Later on this page, we will see a function named CountColor that counts the number of cells in a range that have a specific color index. If you change the color of a cell in the range that is passed to CountColor, Excel will not recalculate the CountColor function and, therefore, the result of CountColor may not agree with the actual colors on the worksheet until a recalculation occurs. The relevant functions use Application.Volatile True to force them to be recalculated when any calculation is done, but this is still insufficient. Simply changing a cell color does not cause a calculation, so the function is not recalculated, even with Application.Volatile True.

Is there a "Dummies" guide to this....???

[Norman]

I take it that you are manually adjusting the font of certain cells. Could you not use some conditional formatting and use that same condition as part of your formula to sum the columns?

[MickLL]

Thanks - I'll need to think about it because I don't immediately see how it helps me to do what I want.

MickLL

[MickLL]

I take it that you are manually adjusting the font of certain cells. Could you not use some conditional formatting and use that same condition as part of your formula to sum the columns?

Yes I am manually changing the font colour. I am not aware of a command that says "if red then add if not red then don't add" (or words to that effect). I'm well familiar with "if a<b then add if it's not then don't".

Having said that your input (and the one above) has given me an idea. It's ugly but would work.

I could introduce a second, adjacent, column in order to enter a 'flag'. If I enter the flag then conditional formatting will alter the colour in my addition column. I could then use the same flag in the column total to decide whether to include or exclude the number. As I said ugly - but it would work!

MickLL

[Norman]

Yes I am manually changing the font colour. I am not aware of a command that says "if red then add if not red then don't add" (or words to that effect). I'm well familiar with "if a<b then add if it's not then don't".

Take a look at conditional formatting in the help system. You can format a cell based on a condition you specify. So you could, for example, set the background colour to red and the foreground (text) colour to white if the value in the cell was a negative value or more than 100 etc. You could then use a hidden column to put a flag if that condition was true.

Or even better, put a zero if the condition is true otherwise set it to the value of the adjacent cell. Then just sum the hidden column and put the result at the bottom of the formatted cells column.

[GeoffR]

You could simply write a formula that specified the cells to be included i.e.
=(Sum(A1:A6))+(A9)+(Sum(A11:A15))

OK if you only want to do it once but if the spreadsheet is to be used regularly with different cells to be included or excluded it won't be much use.

[MickLL]

Maybe I'd better explain what I'm trying to do.

I manage my son's current account because he lives abroad.

I keep a spreadsheet on which I enter start balance, the cheques I've written and end balance. I enter the cheques as I write them. When the cheque appears on the bank statement as having been paid I colour it blue. So I know the calculated balance after all cheques have been paid. Sometimes I need to know the actual statement balance - that is the start balance less the cheques paid but ignoring the cheques written but that have not appeared on the statement.

Got it? Even I'm now confused. An example;

Start Balance 100
cheque 1 20
cheque 2 30
Calculated balance 50
Statement balance 70


Hope that's clear. Maybe I'm being dumb but I don't understand how any suggestions so far achieve what I need.

MickLL

PS Obviously, when I colour the next number blue I'd like an automatic update of the 'statement total'

[Barney]

Rather than colouring cells, why not use an 'if' formula? Add two columns one titled 'Paid' or similar the other for the balance. In the balance column, put in if <cell in Paid column> = Y then result is <cell number eg d43>, if <cell in Paid column> = N then result is 0.

I've probably not explained that very well, but this is the way I'd do it.

[OneTen]

It's possible to do this using VBA but by far the easiest solution is as Norman suggested. Add new columns, one is a flag either Y if the amount appears on the statement or blank if not. Then another column with formula.



E5 contains =IF(D5="Y",C5,0) and copy down for other rows. C8 and E8 are just the simple SUM().

[Barney]

Yeah, that's what I meant!

[john_g]

The magic word is "Reconciled". Most home accounting packages use a column (or equivalent) with this title to tell the software whether the entry appears on the bank statement. If it does, it's reconciled, if it doesn't, it's unreconciled. You generally then get to see both the reconciled balance (the one that appears on your bank statement) and the unreconciled one (that shows you the true balance if all payments are taken into account). Much easier than faffing around with colours and much clearer.

But there are several really good - and free - home accounts packages that will do all this, so why use Excel? For example, I do all my accounts on my phone using JabpLite.

[MickLL]

Thanks guys. Looks like I'm going to have to rebuild the whole sheet (should have said that I've been using it for 2 years).

I was rather hoping that someone would know of a magic command along the lines sum(if blue,A1..A10).

Guess no luck.

Thanks again

MickLL

[Steve52]

You can format the cell so that any '-'(minus) number is in red.
Then all you need do is write a countif statement at the bottom of the column, so that it only counts those figures with a 'minus' in front of it.
You will just have to remember to make sure that any new value you put in has a minus in front of it.
Haven't tried it (don't have EXCEL on home laptop, but do at work), but I think it will work.

[MickLL]

You can format the cell so that any '-'(minus) number is in red.
Then all you need do is write a countif statement at the bottom of the column, so that it only counts those figures with a 'minus' in front of it.
You will just have to remember to make sure that any new value you put in has a minus in front of it.
Haven't tried it (don't have EXCEL on home laptop, but do at work), but I think it will work.

Won't the minus screw up the totals?

MickLL

[OneTen]

Like I said it is possible but you would have to use VBA, a short macro program would be able to take care of it. Inserting the changes that Norman, Barney and I have recommended will not take long at all. Inserting two columns will adjust your other formulae automatically (assuming they are written correctly).

[MickLL]

Like I said it is possible but you would have to use VBA, a short macro program would be able to take care of it. Inserting the changes that Norman, Barney and I have recommended will not take long at all. Inserting two columns will adjust your other formulae automatically (assuming they are written correctly).

Thanks Richard,

You are right - I can make the changes. I'm fundamentally lazy though and was still hoping that there was a 'magic bullet'. As you can imagine it's just a little more complex than I've said because I do his accounts month by month and I'll need to add columns for every month. It's still not too onerous though.

You probably have the impression that I'm no Excel expert - and you would be right so your mention of VBA leaves me in the dark. Is it simple to explain what it is?

MickLL

[Benchista]

Visual Basic for Applications - a development of the old Basic programming language. It's a pretty powerful tool that massively opens up the flexibility of Excel (and other MS Office components), but does require an extra level of understanding to use, and frankly I would go with the extra column for the flag option personally - you can then use conditional formatting to change the colour, total only the required fields and so on, and keep it all at a level where you understand what you're doing - and what you've done when you need to change it.

[MickLL]

where you understand what you're doing - and what you've done when you need to change it.

Cheeky monkey !!!

Just joking - it's very good advice.

MickLL