# Excel Formulae - unable to undestand the formula(e) meaning in an existing excel workbook

1. ## Excel Formulae - unable to undestand the formula(e) meaning in an existing excel workbook

I've been mostly into Python but need to use an existing excel workbook and do some some curve fitting using excel solver

I shall be grateful if some can help me with what these formulae in excel mean

1) =IF(AND(X26="Y",Y26=1), IF(H26/G26>=0, (G26-H26)*(1-H26/G26)^(\$L\$3-1),(G26-H26)*(1-H26/G26)^(\$L\$4-1)), #N/A)

2) =IF(E26=0.127, F26, #N/A)

3) =MAX(1E-50,IF(J11="BROKEN", NORM.DIST(LN(F11), LN(P11), \$R\$5,FALSE), 1-NORM.DIST(LN(F11), LN(P11), \$R\$5, TRUE)))

Thanks a lot for the help
Jo  Register To Reply

2. ## Re: Excel Functions

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)  Register To Reply

3. ## Re: Excel Formulae - unable to undestand the formula(e) meaning in an existing excel workb

Which bits specifically do you not understand?  Register To Reply

4. ## Re: Excel Formulae - unable to undestand the formula(e) meaning in an existing excel workb

I do not follow

a) What the the three items separated by commas mean? That is what each comma indicate?
b) What does the AND indicate int eh beginning of the sentence?
c) What does the \$ symbol indicate  Register To Reply

5. ## Re: Excel Formulae - unable to undestand the formula(e) meaning in an existing excel workb

OK.

1) =IF(AND(X26="Y",Y26=1), IF(H26/G26>=0, (G26-H26)*(1-H26/G26)^(\$L\$3-1),(G26-H26)*(1-H26/G26)^(\$L\$4-1)), #N/A)

If the red section is true, move onto the green section - if the red section is false, return N/A.
If the start of the green section is true (up to the first comma), return the second section (after the comme). If its false, move onto the pink section.  Register To Reply

6. ## Re: Excel Formulae - unable to undestand the formula(e) meaning in an existing excel workb

=IF(AND(X26="Y",Y26=1)

The AND means that both have to be true for the IF to return true.

c) What does the \$ symbol indicate
It's there to lock or anchor cell references so that they don't change as you copy down or across - a dollar sign in front of the letter locks the column, one in front of the number locks the row. Google "Excel absolute and relative cell references".  Register To Reply

7. ## Re: Excel Formulae - unable to undestand the formula(e) meaning in an existing excel workb

Thanks a lot, much appreciated  Register To Reply

8. ## Re: Excel Formulae - unable to undestand the formula(e) meaning in an existing excel workb  Register To Reply

9. ## Re: Excel Formulae - unable to undestand the formula(e) meaning in an existing excel workb

One more question

Considering the below

=IF(AND(J11="UNBROKEN", E11=0.127,D11=650), 0.5*F11,0)

Does it mean ONLY if red, green and yellow are true it returns 0.5*F11 else it returns 0?  Register To Reply

10. ## Re: Excel Formulae - unable to undestand the formula(e) meaning in an existing excel workb

Also, please can someone tell me what the formula means?

=INDEX(Sheet2!\$G\$3:\$G\$301, MATCH(A11&B11, Sheet2!\$B\$3:\$B\$301,0))

What does INDEX and MATC refer to?  Register To Reply

11. ## Re: Excel Formulae - unable to undestand the formula(e) meaning in an existing excel workb

=INDEX(Sheet2!\$G\$3:\$G\$301, MATCH(A11&B11, Sheet2!\$B\$3:\$B\$301,0))

=INDEX(Sheet2!\$G\$3:\$G\$301, is the array where Excel will look for the value to return.

MATCH(A11&B11, is the value Excel will look for in the lookup array.

Sheet2!\$B\$3:\$B\$301,0)) is the lookup array.

Find A11&B11 (A11 and B11 concatenated together) in Sheet2!\$B\$3:\$B\$301 and return what you find on the same row in Sheet2!\$G\$3:\$G\$301.  Register To Reply

12. ## Re: Excel Formulae - unable to undestand the formula(e) meaning in an existing excel workb

IF(AND(J11="UNBROKEN", E11=0.127,D11=650), 0.5*F11,0)

Does it mean ONLY if red, green and yellow are true it returns 0.5*F11 else it returns 0?
Yes - spot on. OR would return a result if ANY of the three returned TRUE:

IF(OR(J11="UNBROKEN", E11=0.127,D11=650), 0.5*F11,0)  Register To Reply

13. ## Re: Excel Formulae - unable to undestand the formula(e) meaning in an existing excel workb

Thank you again, much appreciated

I have an excel sheet which is using the Excel "Solver" and doing a curve fitting

I'm really not able to make out from the charts where and how the curve fitting is being done using the Excel "solver". Can I attach my excel sheet and ask some questions?  Register To Reply

14. ## Re: Excel Formulae - unable to undestand the formula(e) meaning in an existing excel workb

Start a new thread with a suitable title and attach the sheet there.  Register To Reply

15. ## Re: Excel Formulae - unable to undestand the formula(e) meaning in an existing excel workb

Thank you very much. I have done as you adviced  Register To Reply