Excel - formula

[Nacean]

Hey,
I have a question, i want to calculate the maximun of an amount of cells.
But here is the tricky part: a few numbers have a character in it. Now i want excel to ingnore that character?

example: "20", "25", "35", "55*"
the maximum should be "55*" but excel gives "35"??

 

[Nacean]

One thing i've forgot to tell, that could be significant:
the characters are allways positioned at the end of the numbers and the char is allway a star (*).

 

[jonsi]

Assuming that your numbers run in a column commencing at A1

in column B1 add the following
=IF(ISNUMBER(A1),A1,(VALUE(LEFT(A1,((FIND("*",A1))-1)))))
then drag down as appropriate. Any cells containing number* will then display as a number in column B.

=MAX(B1:B101) will then display the MAX for that range.

This may not be the simplest way of doing what you want but it works

hwyl
Jonsi B-)

 

[TonyJollans]

Hi Nacean,

One way ..

[blue][tt] =MAX(IF(A1:A10="",,VALUE(SUBSTITUTE(A1:A10,"*",""))))[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[Nacean]

Thanks for your help,
but unfortunatly my problems got a lot biggger and i think that won't be able to solve it so easaly now.

I have a large excel sheet with 5 or more tabs.
and the cells contain a numeric value and also a specific backgroundcolor ( there are up to 5 different colors).
from a row of cells i need to get the biggest numeric value and also the backgroundcolor of that cell.

 

[TonyJollans]

Hi Nacean,

Sorry, forgot to say on my earlier post that the formula needs array entering.

As far as your change of circumstance goes, there is no way to get the colour of a cell without using code.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[KenWright]

What denotes the colour - is it a condition of the value within it perchance? If there is a logic behind the colouring that can be ascertained from the data within the sheet, then maybe it can be found, but we would need more detail.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------