specfic number formating

Posts: 1

Hi all,

Hoping for some help with a spreadsheet. What I would like to do is format a range of cells such that the number is displayed as one of this group {6 8 10 12 14 16 18 20 22 25 28 30 32 36 40 45 50 55 60 70 80}

I would like the cell number to be rounded up to the next whole number above it from the list above. eg. cell number is 11.6 I want it to display 12, number 60.0001, I want it to display 70 and if 60 exactly display 60.

I am not sure wheter or not excel could do this and how difficult it will be. Hopefully somebody has some idea of wheter or not this is possible with excel and is so how to go about it. Wink

I have looked at conditional formating but I don't know/think this is the solution...


Thanks in advance


James

James,

You could use a combination of funtions, MATCH(NumSerched,RefData,-1) to find the position of the number you are searching relative to your reference data. And the HLOOKUP(Index,TableRef,2) function to return the next whole up number needed.

I have attached a worksheet with my solution, I hope this helps.

Jose Luis


P.D. The range of reference data MUST be in descending order!!

Originally Posted by bailej03

Posts: 1

Hi all,

Hoping for some help with a spreadsheet. What I would like to do is format a range of cells such that the number is displayed as one of this group {6 8 10 12 14 16 18 20 22 25 28 30 32 36 40 45 50 55 60 70 80}

I would like the cell number to be rounded up to the next whole number above it from the list above. eg. cell number is 11.6 I want it to display 12, number 60.0001, I want it to display 70 and if 60 exactly display 60.

I am not sure wheter or not excel could do this and how difficult it will be. Hopefully somebody has some idea of wheter or not this is possible with excel and is so how to go about it. Wink

I have looked at conditional formating but I don't know/think this is the solution...


Thanks in advance


James

On Thu, 26 May 2005 10:10:11 -0500, bailej03
<bailej03.1pnqyv_1117142390.8756@excelforum-nospam.com> wrote:

>
>Posts: 1
>
>Hi all,
>
>Hoping for some help with a spreadsheet. What I would like to do is
>format a range of cells such that the number is displayed as one of
>this group {6 8 10 12 14 16 18 20 22 25 28 30 32 36 40 45 50 55 60 70
>80}
>
>I would like the cell number to be rounded up to the next whole number
>above it from the list above. eg. cell number is 11.6 I want it to
>display 12, number 60.0001, I want it to display 70 and if 60 exactly
>display 60.
>
>I am not sure wheter or not excel could do this and how difficult it
>will be. Hopefully somebody has some idea of wheter or not this is
>possible with excel and is so how to go about it. Wink
>
>I have looked at conditional formating but I don't know/think this is
>the solution...
>
>
>Thanks in advance
>
>
>James

Set up an array that has your numbers in a column in reverse order:

80
70
60
55
....
6

Name that range "tbl"

Use this formula:

=INDEX(tbl,MATCH(A1,tbl,-1))

to round up the number in A1.

If it has to be done in the cell in which you actually enter the value, you
will need to use a VB event macro.




--ron

On Thu, 26 May 2005 10:10:11 -0500, bailej03
<bailej03.1pnqyv_1117142390.8756@excelforum-nospam.com> wrote:

>
>Posts: 1
>
>Hi all,
>
>Hoping for some help with a spreadsheet. What I would like to do is
>format a range of cells such that the number is displayed as one of
>this group {6 8 10 12 14 16 18 20 22 25 28 30 32 36 40 45 50 55 60 70
>80}
>

Just an additional point. In Excel "formatting" usually refers to how a number
is displayed and not to what is actually in the cell.

If you actually want the cell to "contain" 60.0001 but "display" 70, that would
be quite difficult. I suppose a VB script could be written that would
superimpose a picture of the value to display over the cell, but I've not done
anything like that.


--ron