Forecasting Data with a formula

lupus9

New Member
Joined
Jan 5, 2022
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
Hey guys,

I am looking for a formula with which I can forecast Data based on a data table:

e.g. Putting in Expenses of $3,000,000 should give me an infrastructure factor of about 0.96.

The Problem: I tried to do it with the "forecast" formula but the values are just wrong and not in line with the data table. I attached a screenshot:
1641916338777.png


What I am trying to do:
A formula like the "forecast" formula but which forecasts exactly like in the graph, so when I put in 200,000, it should give me 0.5 (instead of 0.82 which I get with "=Forecast")
The gaps where the data is missing should then be linearly approximiated exactly as in the graph. Which excel formula do I need? And how?

Please help me. It is a time sensitive matter.

Thank you in advance!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
I suggest to google for "Excel linear interpolation".
 
Upvote 0
Map1
ABCDEF
150%70%80%95%97%
2S200.0001.000.0001.400.0002.000.0005.000.000
3M200.000800.0001.200.0001.800.0004.400.000
4L200.000800.0001.200.0001.600.0004.000.000
5
6M2.000.00095,154%
724
8relative rowposition in row
9
Blad1
Cell Formulas
RangeFormula
C6C6=FORECAST(B6,OFFSET(A1,,B7,,2),OFFSET(A1,A7,B7,,2))
A7A7=MATCH(A6,$A$2:$A$4,0)
B7B7=MATCH(B6,OFFSET($B$1:$F$1,A7,,,),1)
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

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