formula for extracting a single number from a range in a cell.

  • Thread starter Thread starter Phil
  • Start date Start date
P

Phil

I have a worksheet where I am pulling a price range from
a website (example: (cell b2) $177-$219) and I need to
extract the smaller number for a sum of formula (example:
=SUM(b2(smallest number),c6,f4,c3,f5), some of the other
cells might ahave the same kind of range. any help would
be appreciated. I don't know if this is a simple formula
or not but I'm new to formulas like these.

Thanks for you help.....


Phil
 
Do you mean something like this where:
A B
1 100.00 75.00
2 200.00 35.00
3 50.00 100.00
4 300.00 200.00
5 650.00 410.00 << this is normal Sum()

But in B7 =SUM(SMALL(A1:A4,1),SMALL(B1:B4,1)) = 85.00
The "1" in the small(Range,"1") indicates your choice (2 = next smallest)
HTH
 
Phil,

Assuming that I get the question, this formula will get the smallest value
as long as the format is always $nnn-$mmm.

=MIN(MID(A1,2,FIND("-",A1)-2),RIGHT(A1,LEN(A1)-FIND("$",A1,FIND("-",A1))))

which you can then use in your SUM formula.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I understand what you're looking for, but I'm not sure of your
configuration.

If your data is *all* in one cell, *and* the lower number is *always* on the
left, then try this:

=MID(B2,1,FIND("-",B2,1)-1)

If your data is *all* in one cell, *and* the lower number can be left *or*
right, then try this:

=MIN(MID(B2,1,FIND("-",B2,1)-1),MID(B2,FIND("-",B2,1)+1,6))
--

HTH,

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



I have a worksheet where I am pulling a price range from
a website (example: (cell b2) $177-$219) and I need to
extract the smaller number for a sum of formula (example:
=SUM(b2(smallest number),c6,f4,c3,f5), some of the other
cells might ahave the same kind of range. any help would
be appreciated. I don't know if this is a simple formula
or not but I'm new to formulas like these.

Thanks for you help.....


Phil
 
Back
Top