specfic number formating

[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

 

[jose luis]

James,

You could use a combination of funtions, MATCH(NumSerched,RefData,-1)
to find the position of the number you are searching relative to you
reference data. And the HLOOKUP(Index,TableRef,2) function to retur
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!!

Posts: 1

Hi all,

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

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

I am not sure wheter or not excel could do this and how difficult i
will be. Hopefully somebody has some idea of wheter or not this i
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 i
the solution...


Thanks in advance


Jame

+-------------------------------------------------------------------
|Filename: ExampleForum.zip
|Download: http://www.excelforum.com/attachment.php?postid=3429
+-------------------------------------------------------------------

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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