Sample Function Formulas

First, I want to say Thank You again to everybody for all the help & patience. Is there a source somewhere with sample formulas? The formula wizard is only helpful for single functions, not if I want to combine & I may be dense, but I just haven't gotten the hang of building multiple functions yet, especially when it comes to knowing when to add zeros & ones, for the index & match functions, as an example.

I got some great help on indexing a range from another spreadsheet, but when I tried to use it again on a different range & copied & pasted and changed the ranges, I get #REF!, which I know means I screwed something up, I'm just not sure what & I don't know what the 1's & 0's mean.

=INDEX(RiskLevel,MATCH(1,INDEX(FREQUENCY(E5,RiskScore),0),0))

Thanks once again. If any of the guru's need help with their golf game, let me know & I'm happy to reciprocate!!!!!

Is there a source somewhere with sample formulas

Never what you quite need. This forum has solved many questions and had given many sample formula's. Use them for inspiration and if you cannot come to the correct result, We're here to help.

not if I want to combine & I may be dense, but I just haven't gotten the hang of building multiple functions yet, especially when it comes to knowing when to add zeros & ones, for the index & match functions, as an example.

Use Evaluate function for these type of functions. As a matter a fact: MAKE it a desktop icon!

To help you out with this function I need to know the following:
Where are RiskLevel and RiskScore referring to. Look at the defined names and tell us their range.

1) Relevant formula samples are generally unavailable. The best approach
is usually to play with the formula using a small sample of values where
you know what the end result should be. Then vary the values and/or
the function parameters and note how those changes vary the returned values.

2) The #REF! error occurs when the referenced coordinates do not exist.
Example:
=INDEX(A:A,1,2)
That formula is referencing the first cell in the second column of col_A.
There is no second column of Col_A. The formula returns #REF!

3) RE: =INDEX(RiskLevel,MATCH(1,INDEX(FREQUENCY(E5,RiskScore),0),0))
Assumptions:
RislLevel is a single column list.
RiskScore can be a vertical or horizontal list.
E5 contains a valid RiskScore.

This section: FREQUENCY(E5,RiskScore)
returns an array of RiskScore bins...eg {0,0,1,0}
where the 1 occurs in the bin related to the E5 value.

Typically, the formula would need to be ARRAY ENTERED, by pressing
CTRL+SHIFT+ENTER (instead of just ENTER), to convert the FREQUENCY
function into an array that the MATCH function can use.

However, when the INDEX function refers to a single column or single row
series, or a function that returns a series of values) and the second
paramater is a 0...it converts that series to an array.
So, CTRL+SHIFT+ENTER would not be necessary.

The 3rd parameter of the MATCH function is typically 0 or 1.
0 indicates that an exact match is required and the list can be in any order.
Example:
=MATCH(5,{2,4,6,8},0) returns #NA...5 is NOT in that list.
=MATCH(5,{2,4,5,8},0) returns 3...5 is the 3rd item in that list.

1 indicates that an approximate match is acceptable
(the list must be in ascending order).
Example: =MATCH(5,{2,4,6,8},1) returns 2...4 (the 2nd value) is the highest
value that is less than or equal to 5.

I hope that helps.

Basically you would like to convert the value 0-120 into a catagory Low
Medium, High, High+. A simple If would do the trick

Please Login or Register  to view this content.