 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Mac Forum / Applications / Excel / February 2006Excluding formatted numbers from SUM function?
Hi, I've been trying to figure out how to do something. I keep a very simple spreadsheet of my installed downloads, with a column detailing the memory used by each download, and a SUM function totalling these data. When I delete the intsalled app I simply change the font to red & strikethrough. How can I change the SUM function to exclude any cells where the number is formatted in red & strikethrough? Ahy help greatly appreciated, Cheers, Jason ___ *Replace .invalid with .com for replies* Hi Jason, I did a Google search using Summing Coloured Cells. There are plenty of suggestions supplied. I'm pretty sure you can't use any of the inbuilt functions, font formats are not detectable by any of them (except for the use of red font indicating negative values). Tom Ogilvy suggests a User Defined Function which you could paste into a Module in the workbook's VBA Editor or into your PERSONAL.XLS Macro Workbook. You can then access it as you would any worksheet function. I've changed Tom's code so that it only sums cells with the automatic font colour (ColorIndex value = -4105) Function SumAutoColor(rng As Range) Dim total As Double Dim cel As Range total = 0 For Each cel In rng If IsNumeric(cel) Then If cel.Font.ColorIndex = -4105 Then total = total + cel.Value End If End If Next SumAutoColor = total End Function Ken Johnson On 25/2/06 23:29, in article 1140910188.346257.6440@z34g2000cwc.googlegroups.com, "Ken Johnson" <KenCJohnson@gmail.com> wrote: > Hi Jason, > I did a Google search using Summing Coloured Cells. There are plenty of [quoted text clipped - 22 lines] > > Ken Johnson Fantastic Ken - I REALLY appreciate it. I've inserted a new module into my personal macro workbook & pasted the above code in and voila! It works perfectly! And besides that, it's given me an insight into user-defined functions..hadn't really considered the possibility before. Most appreciated. (Your reply has made it into my 'Reference' folder) Regards, Jason ___ Hi Jason, That's great, thanks for the feedback. Ken Johnson On 25/2/06 23:29, in article 1140910188.346257.6440@z34g2000cwc.googlegroups.com, "Ken Johnson" <KenCJohnson@gmail.com> wrote: > Hi Jason, > I did a Google search using Summing Coloured Cells. There are plenty of [quoted text clipped - 22 lines] > > Ken Johnson Hi Ken, Just a quick follow-up question. Do user-defined functions behave differently to normal functions? In the example above, the SumAutoColor doesn't re-calculate instantly if cell content colors are changed. With a normal SUM, if say, a cell's numerical content is changed, the SUM result is obviously changed instantly. However, SumAutoColor only recalculates (if one of the range cells' colors is changed) when the workbook is saved, closed & re-opened? Is there a reason for this & can it be circumvented? TIA, Jason __ > Just a quick follow-up question. Do user-defined functions behave > differently to normal functions? In the example above, the SumAutoColor [quoted text clipped - 6 lines] > > Is there a reason for this & can it be circumvented? It's not a function of the UDF, per se. The reason is that changing format doesn't fire a recalculation, whereas changing a value used as an argument in a function (whether UDF or built-in) does. You can force recalculation by typing SHIFT-F9 or CMD-SHIFT-= There's nothing much you can do to circumvent this automatically. About the only way I know is to use an OnTime macro to recalculate the worksheet every second or two. On 27/2/06 00:14, in article jemcgimpsey-3A5455.17142626022006@msnews.microsoft.com, "JE McGimpsey" <jemcgimpsey@mvps.org> wrote: >> Just a quick follow-up question. Do user-defined functions behave >> differently to normal functions? In the example above, the SumAutoColor [quoted text clipped - 18 lines] > the only way I know is to use an OnTime macro to recalculate the > worksheet every second or two. Thanks for the info, but SHIFT-F9 just performs a slow expose?, and CMD-SHIFT-= doesn't seem to do anything? Am I doing something wrong? The UDF remains un-recalculated. If I select the cell & <return> at the end of the function line it recalculates? But other than that & saving,closing,opening I can't seem to get it to re-calculate? Cheers, Jason ___ *Replace .invalid with .com for replies* > Thanks for the info, but SHIFT-F9 just performs a slow expose? It took me a minute to figure out what you meant by a "slow expose". I got fed up with Expose after about the first day, and I've disabled all keyboard and hot corner shortcuts. > and CMD-SHIFT-= doesn't seem to do anything? Am I doing something > wrong? The UDF remains un-recalculated. If I select the cell & > <return> at the end of the function line it recalculates? But other > than that & saving,closing,opening I can't seem to get it to > re-calculate? No, you didn't do anything wrong - the keyboard shortcuts won't do anything if there's nothing to indicate that there's a need to calculate on the sheet. To force a function to calculate, even if no other calcs are done on the sheet, add this line as the first line of the function: Application.Volatile Any of the recalc functions will then work - F9, CTRL-=, etc. On 27/2/06 15:07, in article jemcgimpsey-463650.08070127022006@msnews.microsoft.com, "JE McGimpsey" <jemcgimpsey@mvps.org> wrote: >> Thanks for the info, but SHIFT-F9 just performs a slow expose? > [quoted text clipped - 18 lines] > > Any of the recalc functions will then work - F9, CTRL-=, etc. Sorry to be a pain, but I *still* can't get it to work?! I've added the application.volatile as the 1st line of the function, and even disabled the hot corners in case the F9 funcionality was affected. And still nothing? The recalculations remain not done & the shift-F9 and COMM-SHIFT-= still do nothing, but I have been getting a "Compile Error: Invalid outside procedure" message & #NAME? Error in the cell with the udf? When I remove the Application.volatile these error msgs no longer appear. Forgive my stumblings. Jason :-/ Hi Jason, I recognise that error. Looks like the Application.Volatile is not inside the Function. You shouldn't get that error if the code looks like.. Function SumAutoColor(rng As Range) Application.Volatile Dim total As Double Dim cel As Range total = 0 For Each cel In rng If IsNumeric(cel) Then If cel.Font.ColorIndex = -4105 Then total = total + cel.Value End If End If Next SumAutoColor = total End Function Try that Ken Johnson On 27/2/06 23:10, in article 1141081850.241120.144380@p10g2000cwp.googlegroups.com, "Ken Johnson" <KenCJohnson@gmail.com> wrote: > Hi Jason, > I recognise that error. Looks like the Application.Volatile is not [quoted text clipped - 19 lines] > > Ken Johnson That's done it!....I had the application.volatile line at the *very* top... I read JE's post re putting it as the first line of the function & didn't realise that the Function SumAutoColor line was actually just defining the function, so the *next* line would actually be the first line.....Told you I was a Visual Basic virgin ;-) Oh well, I'm learning more every day. Thanks again (you too JE!) Regards Jason ___ Hi Jason, Just curious, are you achieving recalculation via the keyboard or automatically with the OnTime code? I thought I had the OnTime code working OK then JE informed me that while Excel is running it will cause the workbook to automatically re-open 5 seconds after being closed. JE kindly supplied a link where I could find a solution. When, and if, I solve it I'll reply back here. Ken Johnson On 28/2/06 05:47, in article 1141105646.259035.326660@i40g2000cwc.googlegroups.com, "Ken Johnson" <KenCJohnson@gmail.com> wrote: > Hi Jason, > Just curious, are you achieving recalculation via the keyboard or [quoted text clipped - 5 lines] > > Ken Johnson Hi Ken, At the moment I'm recalculating via the keyboard, as I haven't had time to play around with OnTime yet. I would do tonight but I've got a job interview tomorrow so intend doing some preparation. [ he says, while distracting himself with other stuff :-) ] > When, and if, I solve it I'll reply back here. I'd be interested in what you find out, so if you could post any progress that'd be great. Cheers, Jason ___ *Replace .invalid with .com for replies* Hi Jason, I assume you've read JE's reply. I've never used OnTime so I just had to give it a try. After a bit of fiddling around I came up with the following solution: Private Sub Workbook_Open() RptCalc End Sub When the workbook is first opened the above Workbook_Open Sub in the ThisWorkbook Code module starts the next macro, which resides in a standard module... Public Sub RptCalc() Application.OnTime Now + TimeValue("00:00:5"), "CalcNow" End Sub which, after a 5 second delay, runs the next macro residing in the same module... Public Sub CalcNow() Sheets(1).Calculate RptCalc End Sub which calculates the first sheet in the workbook then re-runs the RptCalc macro. This creates repeated calculation of sheet1 every 5 seconds. One other small change is make the original UDF, SumAutoColor, volatile by adding the following line (first line).... Application.Volatile I don't know if this is the best way to achieve repeated calculation. If you (or anybody else reading this post) know of a better way don't hesitate to let me know. Ken Johnson > I've never used OnTime so I just had to give it a try. After a bit of > fiddling around I came up with the following solution: This will work, though the two functions could be combined into one: Public Sub RptCalc() Const dFIVE_SECS As Double = 0.0000578703703703704 Sheets(1).Calculate Application.OnTime Now + dFIVE_SECS, "RptCalc" End Sub There are a few problems with both simple approaches, though - for instance, if you close the workbook containing the macro(s), XL will reopen it when the 5 seconds is reached. You can find information on more sophisticated methods here http://cpearson.com/excel/ontime.htm Thanks for that JE. Ken Johnson On 2/25/06 10:29 AM, in article C026285C.22D3%caesura@mac.invalid, "Jason O" <caesura@mac.invalid> wrote: > Hi, > [quoted text clipped - 12 lines] > ___ > *Replace .invalid with .com for replies* While you are changing the font to red, simply precede the value with an apostrophe.  SignatureBob Greenblatt [MVP], Macintosh bobgreenblattATmsnDOTcom Hi Bob, Funny how the simplest solutions take time to filter through. Oh well, at least I learnt some new VBA. Thanks Bob Ken Johnson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.