* Need serious help with formula/function *

  • Thread starter Thread starter The Flea Circus
  • Start date Start date
T

The Flea Circus

Here's an example of what I need...


Cell Value
_________________

D3 100.0000 <---------------- Random number entered



D7 37.6875

D8 75.3750
<---------------- 100.0000 falls
D9 113.0625 in between here.

D10 150.75000

D11 188.4375

D12 226.1250



D19 _________ <---------------- Target number



I need excel to recognize where the random number ( D3 ) falls on the
scale ( D7 - D12). Then, I need excel to know whether to round up, or
round down, and automatically place the value in cell D19. FYI,
numbers on the scale are in increments of 37.6875.

In the example, 100.0000 falls in between 75.3750 and 113.0625, and
should then be rounded UP because 100.0000 is more than ½ way
between 75.3750 and 113.0625. So the target number would be 113.0625.


I need a formula that will help me achieve this. Any help would be
greatly appreciated.
 
The Flea Circus said:
I need excel to recognize where the random number ( D3 ) falls on
the scale ( D7 - D12). Then, I need excel to know whether to round
up, or round down, and automatically place the value in cell D19.
FYI, numbers on the scale are in increments of 37.6875.
Click to expand...
....

If your tables is really graduated by constant 37.6875 increments,

D19:
=ROUND(D3/37.6875,0)*37.6875

More generally, use the array formula

D19:
=INDEX(D7:D12,MATCH(MIN((D7:D12-D3)^2),(D7:D12-D3)^2,0))
 
...

If your tables is really graduated by constant 37.6875 increments,

D19:
=ROUND(D3/37.6875,0)*37.6875

More generally, use the array formula

D19:
=INDEX(D7:D12,MATCH(MIN((D7:D12-D3)^2),(D7:D12-D3)^2,0))
Click to expand...



Thanks! The first formula worked! Second formula didn't, but who
cares?

Thanks again.
 
Back
Top