• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extrapolating a Number

G

GB

Member
Hi Smarter Ones than me,

I am trying to solve the following problem in a clever way, can you help? I have a number in cell A1 (eg 250) and another in cell B1 (eg 3. Note B1 never changes value). I want to find the square root of A1 then add B1 to this number then re square this value to a factor of 2.


In cell A2 I enter =power(sqrt(A1)+$B$1,2)

In cell A3 I copy the formula from cell A2 and repeat this process many times down the page. I repeat this process until I find all the numbers that fall between eg 2000 & 3000.


How do I do this in one formula, rather than copying down the page?


thanks in advance

regards

Birko
 
in your example, you would have only had to copy the formula down to row 14, and found that there are 3 cells that fall in your range. It appears that each formula is dependent on the result of the prior cell to figure out it's starting value. Now, to find how many cells fall in your range, you could use a pair of COUNTIFs like

=COUNTIF(Range,">="&LowerBoundary)-COUNTIF(Range,">"&UpperBoundary)


If I've misunderstood the question, perhaps you could post an example of your data?
 
Hi Luke,

thanks for responding. In the example I provided you are correct (there are three numbers that are between 2000 & 3000) for my example provided. In reality the value in A1 & B1 will change, therefore the copy down of the formula will need to expand or shrink to find the answers.


I think the best way to handle this is using VBA, so sorry for wasting your time.


regards

Birko
 
You must log in or register to reply here.
Back
Top