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

Using the cell function within other functions

A

Aldo

New Member
Hello,


I am new to the forum and using Excel for data analysis in general, thanks for the help in advance.


I am working with some data and trying to find a slope in a linear region the data. I have been trying to use the SLOPE function and specifying a range in X and its accompanying Y data using the following functions:


Start of X data of interest =CELL("address",INDEX(E17:E90,MATCH(.001,E17:E90,1)))

End of X data of interest =CELL("address",INDEX(E17:E90,MATCH(.005,E17:E90,1)))

Start of Y data of interest =CELL("address",INDEX(F17:F90,MATCH(.001,E17:E90,1)))

End of Y data of interest =CELL("address",INDEX(F17:F90,MATCH(.005,E17:E90,1)))


This works well and I have these functions outputting cells showing this:

____D______E_____F__

3__________X_____Y__

4_Start__$E$31_$F$31

5_Finish_$E$82_$F$82


Now I am trying to use the CELL(“contents”, ##) function inside the SLOPE function to call out these ranges:

=SLOPE(CELL(“contents”,F4):CELL(“contents”,F5),CELL(“contents”,E4):CELL(“contents”,E5))


I know that the slope function given these addresses works when this task is performed manually so I am guessing that there is a better way to store these addresses and use them in SLOPE without using the CELL function.


Thanks for having a look,


Aldo
 
Sorry, that chart should look like this:


____D______E_____F__

3__________X_____Y__

4_Start__$E$31_$F$31

5_Finish_$E$82_$F$82


Aldo
 
Hi Aldo,


Welcome to the Forums!


Is this formula , the 5th one giving you results? I tried it and it does't execute!! Can you post a sample file?


Regards,
 
Thats the problem, using the cell function wont work. In this sheet the slope is found by manually entering the start and stop.


https://www.dropbox.com/s/ck9lvzn1ly2kej1/Sample%20Slope%20File.xlsx


Aldo
 
You don't want to be finding the cell address of the string, you want the actual cell reference then.

=SLOPE(INDEX(F17:F90,MATCH(.001,E17:E90,1)):INDEX(F17:F90,MATCH(.005,E17:E90,1)),

INDEX(E17:E90,MATCH(.001,E17:E90,1)):INDEX(E17:E90,MATCH(.005,E17:E90,1)))


If you already have the cell addresses displayed in E4:F5, you could also do:

=SLOPE(INDIRECT(F4&":"&F5),INDIRECT(E4&":"&E5))
 
You are a Ninja indeed...both of these functions work very well.


Can you elaborate on the difference between an address and a reference in Excel? For instance:


=INDEX(F17:F90,MATCH(.001,E17:E90,1)) returns 4.138029 in this sheet, how does CELL use this number and output E31 using "address"?


Thanks,


Aldo
 
The INDEX function is taking 2 arguement; an array and a number indicating row position. This lets it know which cell to look at. Let's say it's going to look at cell F20. At this point this, the formula is equivalent to saying

=F20

which will of course give the contents of F20. However, we have the INDEX function nested inside another function, SLOPE. SLOPE is looking for a cell reference, and so the F20 gets passed as is:

=SLOPE(F20:....)

with the other INDEX function providing the rest of the formula.


CELL, on the other hand takes F20 and immediately does something with it. In our case, it returns the cell address as a string. This string can't be used by other functions (well, except for INDIRECT). Does that help, or clear as mud?
 
That helps a lot, just wanted to make sure I was following why CELL wouldn't work when nested...it changes the format to a string.


Thanks,


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