Index Match & round vs specific cell by cell calculation

Greenbehindthecells

Board Regular
Joined
May 9, 2023
Messages
50
Office Version
  1. 365
Platform
  1. Windows
Good afternoon,

I hope I can make this clear... I would like to make a formula to calculate the formula in I2 (Formula sheet) =ROUND(Numbers!K2*(100%-E4)*D4,1)+C4 that doesn't need to be so specific where I have to jump back and forth between my two sheets to locate and select the correct reference cells (Numbers Key column & Place headers) to make this formula work. I have another index match formula that is doing the complex work for me in another column in my 'formula'. sheet I was wondering if looking at these sheets, there is a way to use a formula to search for the correct place header row (j1:s1) in the Numbers sheet and the key column (i2:i24) to replace the first part of my formula '(Numbers!K2...'. I am not at all familiar with index match, I received assistance for that formula. The actual sheet is not as organized as this copy, so it would need to be able to handle that it is out of order. Any assistance will be greatly appreciated!

First sheet 'Numbers:
Book1
ABCDEFGHIJKLMNOPQRST
1C header 1C header 2C header 3C header 4C header 5C header 6C header 7C header 8Key<5th5th10th25th33rd50th66th75th90th95th
2aa1.01%2.01%3.01%4.01%5.01%6.01%7.01%8.01%9.01%10.01%
3bb11.01%12.01%13.01%14.01%15.01%16.01%17.01%18.01%19.01%20.01%
4cc21.01%22.01%23.01%24.01%25.01%26.01%27.01%28.01%29.01%30.01%
5dd31.01%32.01%33.01%34.01%35.01%36.01%37.01%38.01%39.01%40.01%
6ee41.01%42.01%43.01%44.01%45.01%46.01%47.01%48.01%49.01%50.01%
7ff51.01%52.01%53.01%54.01%55.01%56.01%57.01%58.01%59.01%60.01%
8gg61.01%62.01%63.01%64.01%65.01%66.01%67.01%68.01%69.01%70.01%
9hh1.01%2.01%3.01%4.01%5.01%6.01%7.01%8.01%9.01%10.01%
10ii11.01%12.01%13.01%14.01%15.01%16.01%17.01%18.01%19.01%20.01%
11jj21.01%22.01%23.01%24.01%25.01%26.01%27.01%28.01%29.01%30.01%
12kk31.01%32.01%33.01%34.01%35.01%36.01%37.01%38.01%39.01%40.01%
13ll41.01%42.01%43.01%44.01%45.01%46.01%47.01%48.01%49.01%50.01%
14mm51.01%52.01%53.01%54.01%55.01%56.01%57.01%58.01%59.01%60.01%
15nn61.01%62.01%63.01%64.01%65.01%66.01%67.01%68.01%69.01%70.01%
16oo1.01%2.01%3.01%4.01%5.01%6.01%7.01%8.01%9.01%10.01%
17pp11.01%12.01%13.01%14.01%15.01%16.01%17.01%18.01%19.01%20.01%
18qq21.01%22.01%23.01%24.01%25.01%26.01%27.01%28.01%29.01%30.01%
19rr31.01%32.01%33.01%34.01%35.01%36.01%37.01%38.01%39.01%40.01%
20ss41.01%42.01%43.01%44.01%45.01%46.01%47.01%48.01%49.01%50.01%
21tt51.01%52.01%53.01%54.01%55.01%56.01%57.01%58.01%59.01%60.01%
22uu61.01%62.01%63.01%64.01%65.01%66.01%67.01%68.01%69.01%70.01%
23vv21.01%22.01%23.01%24.01%25.01%26.01%27.01%28.01%29.01%30.01%
24ww31.01%32.01%33.01%34.01%35.01%36.01%37.01%38.01%39.01%40.01%
25
26
Numbers


Second sheet 'formulas'


Book1
ABCDEFGHIJK
1
25th10th
3KeyNumDenRatePercentiledeficittargetdeficittarget
4Blah1aa11010.00%90th0.21.2
5Blah2bb22010.00%<5th
6Blah3cc33010.00%<5th
7Blah4dd44010.00%<5th
8Blah5ee55010.00%<5th
9Blah6ff66010.00%<5th
10Blah7gg77010.00%<5th
11Blah8hh88010.00%90th
12Blah9ii99010.00%<5th
13Blah10jj1010010.00%<5th
14Blah11kk1111010.00%<5th
15Blah12ll1212010.00%<5th
16Blah13mm1313010.00%<5th
17Blah14nn1414010.00%<5th
18Blah15oo1515010.00%90th
19Blah16pp1616010.00%<5th
20Blah17qq1717010.00%<5th
21Blah18rr1818010.00%<5th
22Blah19ss1919010.00%<5th
23Blah20tt2020010.00%<5th
24Blah21uu2121010.00%<5th
25Blah22vv2222010.00%<5th
26Blah23ww2323010.00%<5th
27
Formula
Cell Formulas
RangeFormula
E4:E26E4=IFERROR(C4/D4,"0.00%")
F4:F26F4=INDEX(Numbers!$J$1:$S$1,,IFERROR(MATCH(Formula!$E4,INDEX(Numbers!$J$2:$S$24,MATCH(Formula!$B4,Numbers!$I$2:$I$24,0),0),1),1))
H4H4=I4-C4
I4I4=ROUND(Numbers!K2*(100%-E4)*D4,1)+C4
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
How about
Excel Formula:
=ROUND(INDEX(Numbers!$J$2:$S$24,MATCH(B4,Numbers!$I$2:$I$24,0),MATCH(F4,Numbers!$J$1:$S$1,0))*(100%-E4)*D4,1)+C4
 
Upvote 1
Solution
How about
Excel Formula:
=ROUND(INDEX(Numbers!$J$2:$S$24,MATCH(B4,Numbers!$I$2:$I$24,0),MATCH(F4,Numbers!$J$1:$S$1,0))*(100%-E4)*D4,1)+C4
Thank you. I tried that, but the calculation was off by 2, I am not sure why. I had to use the round function due to finding a .125% discrepancy in the formula I made. I removed the round, but that did not sort out the calculation.

Is the first part 'Numbers!$J$2:$S$24' supposed to be calling to the "<5th" column? I am trying to find what numerator increase is needed for 5ht, 10th, etc places rank.
 
Last edited:
Upvote 0
Thank you. I tried that, but the calculation was off by 2, I am not sure why. I had to use the round function due to finding a .125% discrepancy in the formula I made. I removed the round, but that did not sort out the calculation.

Is the first part 'Numbers!$J$2:$S$24' supposed to be calling to the "<5th" column? I am trying to find what numerator increase is needed for 5ht, 10th, etc places rank.
I am sorry, I think I understand... the F4 is calling to the current percentile(in the formula's sheet). I am trying to forecast for the next percentile up (the rate in the numbers sheet). I was able to change F4 to the header in the numbers sheet (k1). Thank you so much! I appreciate your time and how much you have helped me! @Fluff
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 1

Forum statistics

Threads
1,224,820
Messages
6,181,160
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top