Re: How do I count the number of brackets within a formula (cell)?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

John's UDF is probably safer, but this is another approach, which may or may
not be more trouble then it's worth, where you "Get" the cell contents,
whether it's a formula or data, and then parse it to count your trips.

Say the column with your kilometer formula is 2 columns to the left of the
column where you would like to use a new formula to return the number of
trips.

km formula in Column A - Trips in Column C

Select cell C1, then:

<Insert> <Name> <Define>

In the "Names In Workbook" box, enter any name, say
FORM
short for formula.

Then, click in the "Refers To" box, hit <F2> to enter the edit mode, and
enter this formula:

=GET.CELL(6,Sheet1!A1)

Then click <OK>

You now have a relative formula, which will access a cell 2 columns to the
left of *wherever* it's entered.

With your km formula in A1, enter
=form
in C1
And you'll get
=(5-2)+(6-2)+(8-3)
in C1

We can now parse *IN* C1 to count the parenthesis and return your number of
trips:

Enter this formula in C1:
=LEN(form)-LEN(SUBSTITUTE(form,"(",""))

Like I said, maybe more trouble then it's worth, but interesting none the
less.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"paper cutter" <paper cutter@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CFC71FF9-EF81-4A98-A441-F20973F0076A@xxxxxxxxxxxxxxxx
> I am reviewing vehicle usage. An example of a formula under review is as
> follows: =(5-2)+(6-2)+(8-3). This will add up the kilometers. The 2nd
> column (the problem), I want to calculate is the number of trips. Each
> bracket is considered one trip. In the above example that would be a
total
> of 3 trips and total of 12 km.


.

Relevant Pages

  • Re: Virginias DOT get involved in land-use
    ... you can buy a certain plan and if you exceed what your plan allows, ... The way the toll idea would work.. ... exceeded your cell phone minutes. ... One of the choices would be to have no trips and to pay for each one... ...
    (misc.transport.road)
  • Re: validation?
    ... With the number of months per trip in cell G1, ... cost calculations. ... > calculate for a certian number of trips per year. ... > year by the cost of a flight, which in this case is 2,500. ...
    (microsoft.public.excel)
  • Re: Im actually going on a boat
    ... be enough to keep me busy during the times at sea. ... My trips for work were pretty boring during after work hours. ... Watch the use of your cell phone on board the ship. ... Cresent City Cell. ...
    (rec.boats)
  • Re: Virginias DOT get involved in land-use
    ... you can buy a certain plan and if you exceed what your plan allows, ... The way the toll idea would work.. ... exceeded your cell phone minutes. ... One of the choices would be to have no trips and to pay for each one... ...
    (misc.transport.road)
  • Re: Harrahs getting pretty strange
    ... When not playing poker I like playing the magic mode penny slots for fun ... As yet this year I have not lost more in a casino than my comps are worth ... On those trips I never lost more that $1,000 on any given ...
    (rec.gambling.poker)