question

KARFUNG-9753 avatar image
0 Votes"
KARFUNG-9753 asked VikiJi-MSFT commented

Query of index & match formula in MS Excel

Hi Sir,

I have created a formula using index and match formula as per picture & attachment. However, the answer didn't reflect the correct value. Kindly advise.

For your info, Tension and Shear reduction factors are similar as per columns F & G, therefore the formulas were similar. I have no issue with the reduction factors at columns F & G, rows 12 to 14 where data was index from table RFCS.

However, the reduction factor at cell F15 is referenced with table RFED. Cell F15 shows incorrect answers from the data and formula given. For example, 190 in cell E15 shall indicate the value 0.89 instead of 0.72 at cell F15. Kindly advise what is adjustment required that cell F15 shows 0.89.

145470-image.png




File as per link:

https://mega.nz/file/VaxGkBLD#duauhTPqiW3q6gZUpeq2gwitp4CqP9ec062S0RYJPQM

office-excel-itpro
image.png (64.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

dF9JYLaf avatar image
0 Votes"
dF9JYLaf answered VikiJi-MSFT commented

Hello @KARFUNG-9753 in formula of cell F15, in second MATCH should you not reference ActualEdge (instead of ActualSpacing)? That would return value 0.89.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @KARFUNG-9753,
Welcome to Q&A forum!

Per my test, please refer to dF9JYLaf's reply to reference ActualEdge instead of ActualSpacing in cell F15.

145626-image.png


If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

0 Votes 0 ·
image.png (100.2 KiB)