Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
the sum of all bolded numbers
i have a column of prices (ie - $25, $40...) and i want to be able to acquire only the sum of the numbers that i bold in that paticular column while keeping the unbolded numbers ignored for the equation is this at all possible, if so how? thanks for any help -- Sam12345 ------------------------------------------------------------------------ Sam12345's Profile: http://www.excelforum.com/member.php...o&userid=11882 View this thread: http://www.excelforum.com/showthread...hreadid=486695 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
the sum of all bolded numbers
There does not appear to be a way to determine if a cell is bolded
using the built-in functions of Excel. However, you can use VBA, see http://www.cpearson.com/excel/colors.htm An alternative to VBA is to use an adjacent column to hold the indicator of the condition that causes you to bold the prices. Say you put a 1 in that column and use Conditional formatting, if you need, to also bold the prices if the adjactent column equals 1. Then you can easily use SUMPRODUCT to sum those prices that have that condition. However, this approach will not catch prices that you explicitly bold. Nevertheless, it might help usability if the reason for bolding were obvious in an adjacent column with an appropriate header. HTH DOR |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
the sum of all bolded numbers
You could use a UDF
Function SumBold(rng As Range) Dim cell As Range For Each cell In rng If IsNumeric(cell.Value) Then If cell.Font.Bold Then SumBold = SumBold + cell.Value End If End If Next cell End Function Call such as =SumBold(A2:a20) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Sam12345" wrote in message ... i have a column of prices (ie - $25, $40...) and i want to be able to acquire only the sum of the numbers that i bold in that paticular column while keeping the unbolded numbers ignored for the equation is this at all possible, if so how? thanks for any help -- Sam12345 ------------------------------------------------------------------------ Sam12345's Profile: http://www.excelforum.com/member.php...o&userid=11882 View this thread: http://www.excelforum.com/showthread...hreadid=486695 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
the sum of all bolded numbers
Note that this doesn't update when you bold/unbold cells.
If you put the line Application.Volatile as the second line, it will update whenever any cell in the sheet is calculated. Or you could hit F9. In article , "Bob Phillips" wrote: You could use a UDF Function SumBold(rng As Range) Dim cell As Range For Each cell In rng If IsNumeric(cell.Value) Then If cell.Font.Bold Then SumBold = SumBold + cell.Value End If End If Next cell End Function |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
the sum of all bolded numbers
that's cool - i could make that work however i am completely unaware of what vba is or how it works. could someone please explain this to me -- Sam12345 ------------------------------------------------------------------------ Sam12345's Profile: http://www.excelforum.com/member.php...o&userid=11882 View this thread: http://www.excelforum.com/showthread...hreadid=486695 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
the sum of all bolded numbers
VBA stands for "Visual Basic for Applications". It is a
programming language that can be used to create programs, also called macros, which can be used to process the data in your spreadsheet and perform other actions relative to your spreadsheet that you may wish to automate. UDF stands for "User Defined Function". You are obviously familiar with the Excel's built-in functions such as the SUM function. Sometimes, as in your current problem, the built-in functions may not do what you want to do. It is possible, using VBA, to develop a new function that can be invoked from a spreadsheet cell, just as the SUM function is. You can now put that function in a cell in your worksheet and it will perform the actions you have coded it to do. To make this happen, you must store the code of the UDF in a container called a "module", associated with your workbook. This module is different from your worksheets. It is accessed through the Visual Basic Editor (VBE), which is accessed by clicking Alt-F11 with your spreadsheet open. A description of how to implement UDFs is contained here http://www.ozgrid.com/VBA/Functions.htm and in many other sites on the web - just enter "excel user defined functions" in Google. After you have stored a function, such as the one provided by Bob, appropriately modified with JE's modification, in a module, you can simply enter the formula for that function in a cell, e.g. =SumBold(A2:A20) and that formula will provide you with the sum of all the bolded numbers in the range A2:A20, each time the spreadsheet recalculates. Note, however, that a value in some cell must change to cause recalculation unless you invoke it by pressing F9. Merely changing the font of a number from unbolded to bolded or vice versa will not cause recalculation, even with JE's modification. DOR |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formulas to numbers | New Users to Excel | |||
Count comma separated numbers, numbers in a range with dash, not t | Excel Discussion (Misc queries) | |||
Checking Winning Numbers in the Lottery. | Excel Discussion (Misc queries) | |||
Count and Sum Total occurrances of two specific numbers | Excel Worksheet Functions | |||
Converting Numbers to Text properly | Excel Discussion (Misc queries) |