usin < as part of my data

[sandraleeann]

ok, so I am a self taught excel student so bear with me. I have a column of data and some are regular numbers ... 1.5 2.6 1.0 0.9 etc, but some are <1.2 <1.9 <2.5... now how can I make a formula that will multiply this data with data from an adjcent column and a constant of 8.34. I want the actual number after the < to be used in the product, and can I make my answer force a < symbol when there is one being used in the calculation... I hope this makes sense.

[NBVC]

Try:

=IF(LEFT(A1)="<","<","")&SUBSTITUTE(A1,"<","")*B1*8.34

or

=IF(LEFT(A1)="<","<"&MID(A1,2,10)*B1*8.34,A1*B1*8.34)

The latter will keep the numbers as numbers instead of convert to text if the column A cell doesn't have a "<".

[sandraleeann]

Im so excited it worked. thanks... so i decided to try the second equation first and works great. is there a way to get the result to only show whole number unless the value is less than 10, then would only have one decimal... thanks your awsome

when I use the second method, the cells that use all numerical values keep the format to show no decimals, but the ones that use the < all show the decimals.

any ideas?
Thanks

[Haseeb Avarakkan]

Is this do you mean??

=IF(LEFT(A1)="<","<","")&TEXT(SUBSTITUTE(A1,"<","")*B1*8.34,"[<10]0.0;-0;0")

[NBVC]

The second part of your post seems to be a different question than the first part... it seems that Haseeb has given you solution for the first part...

for second part (if a different question) try: =IF(LEFT(A1)="<","<"&MID(A1,2,10)*B1*8.34,ROUND(A1*B1*8.34,0))

this rounds the number to the next whole number if A1 contains only a number... but it will keep decimals if A1 starts with "<"

[sandraleeann]

Thanks.
Haseeb's solution is what I was looking for. But now another issue. when I want to make an average of these cells, it gives me div#/0 error. any ideas?
I have also tried to sum the cells but that gives zero as well

[Haseeb Avarakkan]

Try this ARRAY FORMULA, must hit CTRL+SHIFT+ENTER, rather than just ENTER.

=AVERAGE(IF(ISNUMBER(--SUBSTITUTE(0&C1:C10,"<","")),--SUBSTITUTE(0&C1:C10,"<","")))

If you can attach a dummy file would be helpful.