Tricky goal seek similar question

[anro]

Hi,

I have a fixed value in cell A1, say 15000

I then have a value is cell A2, which is the sum of a number of factors, including one of the numbers in a range A9:A17 (values from 40-48). What I want is to find a formula that pics the correct number in the range to equal A2 as close as possible to the value in cell A1. The formula could be written in, say cell A20.

Basically, I guess this formula must look somhow at A1, and understand what the "target" is, then at A2 and understand which number from the range A9:A17 it must pick for A2 to become as close as possible of the target (slightly below, not above).

Not sure if this is possible though, but would really appreciate some help on this! Looking forward to inputs!

Thanks a lot! Appreciate the effort of everyone helping out on this board.

 

[shg]

If the numbers in A9:A17 are 40, 41, ..., 48, then isn't the answer just

=MAX(40, MIN(A1-B1, 48))

 

[anro]

Hi SHG, Thank you for your reply.

MAX(40, MIN(A1-B1, 48)) . I assume you mean MAX(40, MIN(A1-A2, 48))? This will produce a circular. Also, I need the formula to look at the range (in the example values from 40-48, but could be 2, 2.3, 7) because these are spesific numbers which other cells are look at.

Does this makes sense? Hope you can help.

 

[shg]

Sorry, I don't understand your data layout. Perhaps you could post it using Excel Jeanie or similar.