Got something to say or just want fewer pesky ads? Join us... 😊

Today's Excel Challenge...



matt

Well-known member
Mar 19, 2007
1,563
How can I distinguish between values in a column so that some are treated as paid and some as outstanding.

For example, if the bold values below are paid and the unbold are unpaid (and values are likely to switch from unpaid to paid or vice versa) how can I dynamically calculate the total of the paid and unpaid values?

Item 1 100.00
Item 2 500.00
Item 3 400.00
Item 4 100.00
Item 5 600.00
---------------
Total Unpaid xxxx.xx
Total Paid xxxx.xx

Ideally I would like to be able to easily and visually mark a value as paid or unpaid (cell or text colour maybe). I'm using Excel 2010 (Windows).

Any ideas?

Cheers :ffsparr:
 




Buzzer

Languidly Clinical
Oct 1, 2006
26,121
Use the SUMIF function and colour in the cells?

=SUMIF(A1:A5,"Blue",A1:A5) and =SUMIF(A1:A5,"Green",A1:A5)


EDIT - but easier would probably be to use another column with P and U and sumif based on those, I reckon.
 


matt

Well-known member
Mar 19, 2007
1,563
It's a pretty wide spreadsheet as it is, covering 15 months, so that may add to its unwieldiness. Any other options?
 








Papak

Not an NSC licker...
Jul 11, 2003
2,252
Horsham
Or change the colour of paid or unpaid, filter by text colour and use =subtotal(9,range) to add either the paid or unpaid.
 


essbee

New member
Jan 5, 2005
3,656
Use R instead - a much more powerful and elegant freebie stats package.

Takes a bit of learning - but you'll never look back.
 


matt

Well-known member
Mar 19, 2007
1,563
Or change the colour of paid or unpaid, filter by text colour and use =subtotal(9,range) to add either the paid or unpaid.

Sooo, I would change the text colour of paid items to blue for example, and then in the formula where and how would I specify that I only want to include 'blue' values?
 






Papak

Not an NSC licker...
Jul 11, 2003
2,252
Horsham
You filter by text colour (right click on a filtered column for the options) the subtotal formula using 9 only sums the currently filtered for rows.

I can make a mock up and stick it on dropbox if you want.
 










KZNSeagull

Well-known member
Nov 26, 2007
20,996
Wolsingham, County Durham
OK, this works in Excel 2000. I haven't got 2010, but I don't see why it will not work in that:

Open up the Visual Basic Editor (Alt F11) and insert a new module:

Function SumIfBold(MyRange As Range) As Double


Dim cell As Range
For Each cell In MyRange
If cell.Font.Bold = True Then
SumIfBold = SumIfBold + cell
End If
Next cell


End Function

In your spreadsheet, in an empty cell put =SumIfBold(A1:A5) replacing A1:A5 with your range of cells.

You will probably then have to save the spreadsheet and reopen it. If it asks you to Enable Macros, agree and then your cell should have the total of the bold cells. I don't think the cell will automatically update if you change something to bold, so you may have to go back to the cell with the call to the function and pretend to edit the call to the function and it will recalculate it then.
For non bold cells, you can create the total of all cells minus the total of bold.
 




Papak

Not an NSC licker...
Jul 11, 2003
2,252
Horsham
Thank you very much. Ideally I would like to keep all the values visible rather than filtering them - is that possible with this approach?

No but you could duplicate the sheet in another tab and have 1 displaying the paid total and the other the unpaid these values could then be displayed together on a 3rd sheet.

Ousethe VBA solution above assuming that it works.
 


matt

Well-known member
Mar 19, 2007
1,563
OK, this works in Excel 2000. I haven't got 2010, but I don't see why it will not work in that:

Open up the Visual Basic Editor (Alt F11) and insert a new module:

Function SumIfBold(MyRange As Range) As Double


Dim cell As Range
For Each cell In MyRange
If cell.Font.Bold = True Then
SumIfBold = SumIfBold + cell
End If
Next cell


End Function

In your spreadsheet, in an empty cell put =SumIfBold(A1:A5) replacing A1:A5 with your range of cells.

You will probably then have to save the spreadsheet and reopen it. If it asks you to Enable Macros, agree and then your cell should have the total of the bold cells. I don't think the cell will automatically update if you change something to bold, so you may have to go back to the cell with the call to the function and pretend to edit the call to the function and it will recalculate it then.
For non bold cells, you can create the total of all cells minus the total of bold.

Ooooooh cool - thank you. If I wanted to use blue text rather than bold as the variable, what would I change? Also, this doesn't change on the fly as such, is there any way to refresh all the formulae in the file? Obviously you may have other things to do....
 


KZNSeagull

Well-known member
Nov 26, 2007
20,996
Wolsingham, County Durham
Ooooooh cool - thank you. If I wanted to use blue text rather than bold as the variable, what would I change? Also, this doesn't change on the fly as such, is there any way to refresh all the formulae in the file? Obviously you may have other things to do....

You can change the bit that says If cell.Font.Bold = True Then to If cell.Font.ColorIndex = <the number of the colour you want to test> Then

Colour numbers are here: http://msdn.microsoft.com/en-us/library/office/ff840443(v=office.15).aspx

So for black, the code would read If cell.Font.ColorIndex = 1 Then.

This does not appear to work if the text colour is automatic, by the way. You would have to change those to black first. You would have to have a seperate function for each colour, as to find the colour in a cell first complicates matters (but is not impossible). If you want that, I will have to play with that one.

No, it will not change it on the fly as you are not actually changing the value in the cell, so the function will not be called. To get it to recalculate the cell, click on the cell with the formula in, place your cursor up in the formula line at the top of the spreadsheet, then press tab right, and the cell should be recalculated (that's what I meant by pretend to edit the formula)
 








Buzzer

Languidly Clinical
Oct 1, 2006
26,121
If you add the line

application.volatile

at the beginning of the function then every time you press F2 and return it will refresh.....


...I think.

I think wrong. The latter is no different from KZN's edit function and the former doesn't get recognised by the application.volatile line. I'll crawl back in my hole.
 


Albion and Premier League latest from Sky Sports


Top
Link Here