* Need serious help with formula/function *

[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.

 

[Harlan Grove]

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.

....

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(D712,MATCH(MIN((D712-D3)^2),(D712-D3)^2,0))

 

[The Flea Circus]

...

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(D712,MATCH(MIN((D712-D3)^2),(D712-D3)^2,0))

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

Thanks again.

 

[Harlan Grove]

"Harlan Grove" <[email protected]> wrote:

....
[2nd formula]

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

I did mention that the second formula was an array formula. Type the
formula, then hold down [Shift] and [Ctrl] keys before pressing
[Enter].

 

[The Flea Circus]

"Harlan Grove" <[email protected]> wrote:

...
[2nd formula]

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

I did mention that the second formula was an array formula. Type the
formula, then hold down [Shift] and [Ctrl] keys before pressing
[Enter].

You're right. My apologies. The second formula worked great too.

thanks again.