ExcelBanter - View Single Post - How in to parse constants in formula to cells

Ron Rosenfeld

On Mon, 27 Nov 2006 18:11:58 GMT, wrote:

We're getting closer to defining what you want.

Ron,

For ROUND(0.035,1) I do not need either of those constants nor do I need the constants in
Average[1,2,3]

Maybe another way to thing of it might me only the full string of numbers after each operator and
the equal sign like "+/-=*". I believe that will give me just the constants which should have been
in a summed worksheet list with labels.

It gets a bit more dicey when IF(X0, A1*3,A1). In this case, I want the 3 but not the 0. But the
3 should be picked up by "the full string of numbers after each operator"

What I realized later is that if the formula had four constants and a link, then a sum of all of the
constants would not add up to the original cell value in this case.

The correct cell value would be the sum of the four constants AND the value of the linked cell.

Because this gets real complicated, I thought that if all functions and the with-in constants would
be appended to the list as its own label ROUND(999.99,2).

Therefore,
= 1+2+3+4+Round(999.99,2) 'Would yield

Description
Item A 1
Item B 2
Item C 3
Item D 4
'Round(999.99,2) 1000 'if complicated to ascertain function's value, leave the value blank
Total 1010

Hopefully, I am clearer now. I'll be close by for the next 6 hours.

Aside from the fact that ROUND(999.99,2) -- 999.99 and not 1000, there remains
a problem in differentiating functions from which you want to extract constants
from those you don't wish to execute an extraction.

IF(X0,A1*3,A1)

you write extract the 3 using the "number string after operator algorithm"/

BUT that fails with

IF(X0,3*A1,A1)

or even

IF(X0,3,1)

==========================================

It seems to me it would be a whole lot simpler to develop a user form that
would ensure that entries are made properly.

--ron