Are You Trendy? (Part 2)
Forecasting using Excel Functions
“Todays forecast will be Hot and Humid with a Chance of Snow?”
(Even the experts with big computers get it wrong)
In the previous post we looked at Manual Forecasting techniques and how Excel can be used to assist. In this post we will look at how we can use Excel built in functions to aid us in forecasting.
This post is going to delve slowly at first and then deeper into some of Excels Statistical Functions. Readers are encouraged to follow along at your own pace and use the examples in the Examples Workbook attached.
All charts, tables and diagrams in this post with the associated Excel formulas are included in the Example workbook.
In this post I will be using the following nomenclature
^ means raise to the power eg: 10^2 = Power(10,2) = 100
. means multiply eg 10.2.M.X = 10.2 * m * x
Why do we need to use Excel Functions?
In the first post we looked at some simple data with only a few points and a trend that was very fairly obvious or was it.
A number of other linear trends could have equally been used and all look about right.
However in real life data is rarely this simple.
Fortunately Excel has a Number of Functions and Tools that allow us to look for trends and use the data natively for forecasting purposes.
There are a number of standard types of trends which can be classified as:
Linear – Approximating a straight line
Polynomial – Approximating a Polynomial function to a power
Power – Approximating a power function
Logarithmic – Approximating a Logarithmic line
Exponential – Approximating an Exponential line
Excel supports the use of these trend types in a number of ways.
Excel Functions and Tools
Excel has a number of Worksheet functions specifically designed to assist us with analysing various trends.
They are categorised by type below
Excel Functions for Linear Trends
 Slope
 Intercept
 Linest
 Trend
 Forecast
Excel Functions for Exponential Trends
 Logest
 Growth
Other Excel Tools
 Excel Chart + Trendline
USING EXCELS WORKSHEET FUNCTIONS
Linear Estimates
In the first Post we looked at using a linear equation in the form Y=mX + c to express our estimated line of best fit which we manual estimated was linear.
Excel has 2 functions which we can use to calculate the actual slope (m) and intercept (c) for the above equation.
Slope
The Slope function returns the slope or gradient of the linear regression line through data points in Known_Y’s and Known_X’s.
eg: =SLOPE(Known Y values, Known X values)
Intercept
The Intercept function calculates the point at which a linear regression line will intersect the Yaxis by using existing Xvalues and Yvalues.
eg: = INTERCEPT (Known Y values, Known X values)
Use
To use the above 2 equations we simply enter 2 equations in cells
m = SLOPE(C47:C51, B47:B51) = 1.298
c = INTERCEPT(C47:C51, B47:B51) = 0.140
We can now use our revised linear equation to plot a line of best fit
Y = m.X + c
Y = 1.298.X + 0.140
So for
X = 5, Y= 6.63 &
X = 30, Y = 39.07
Which we can plot as a new series on our chart
Linest
The Linest function can be used to calculate the Slope and Intercept parameters for a linear function
Linest is an array formula which must be entered as an array formula to return all the values that it can return.
Eg: = LINEST(Known Y Values, Known X Values,Const , Stats)
=LINEST(C47:C51,B47:B51,TRUE,FALSE) will return the Slope (m) component of the equation
Const = True b parameter is calculated
False b is set to 0 (zero)
Stats = True Return additional regression statistics
False Return the m coefficient and const b
To return both components you must enter the same formula in adjacent cells in the same row
and the equation must be entered as an array formula
Eg: = LINEST(C47:C51, B47:B51, TRUE, FALSE) Ctrl Shift Enter
Slope (m)  Intercept (c)  
Linest  1.298  0.140 
Alternatively the values can be retrieved from the Linest array function using the Index function
Gradient m =INDEX(LINEST(C47:C51, B47:B51, TRUE, FALSE),1)
Intercept c =INDEX(LINEST(C47:C51, B47:B51, TRUE, FALSE),2)
The use of the Index function negates the requirement to use an Array Entered formula.
Stats
Linest can also return a number of statistics when Stats parameter is set to True
Eg: =LINEST(C47:C51, B47:B51, TRUE,TRUE) Ctrl Shift Enter
This must be entered as an array formula of 2 columns by 5 rows
The formula can also be entered as a normal equation also using the Index function to extract the array values
Eg: = INDEX( LINEST($C$47:$C$51, $B$47:$B$51, TRUE, TRUE), Row ,Column)
If you want to know the r2 value (discussed later) it is in the 3^{rd} row, 1^{st} column.
Eg: = INDEX( LINEST($C$47:$C$51, $B$47:$B$51, TRUE, TRUE), 3 , 1)
The above table shows the statistic and the value for our example above using both array entered and Index formulas
The r2 parameter highlighted will be discussed later.
Trend
The Trend function is used to calculate a straight line best fit line based on a number of known X & Y values.
Values of Y can be calculated for values of X inside or outside the know range of X values and so Trend can be used to interpolate or extrapolate data.
eg: = INTERCEPT (known Y values, known X values, New X Value, Const)
Const = True; Calculate the Intercept value
= False; Set the Intercept value c = 0
If for example you are using this to model your power cost.
If you have a fixed monthly cost plus a cost per kW, you would set Const to True
If you have no fixed monthly cost and are only charged per kW set Const to false
eg: =TREND($C$101:$C$105,$B$101:$B$105,B106,TRUE)
Forecast
The Forecast function is used to calculate a straight line best fit line based on a number of known X & Y values.
Values of Y can be calculated for values of X inside or outside the know range of X values and so Trend can be used to interpolate or extrapolate data.
eg: = FORECAST (New X Value, Known Y values, Known X values)
= FORECAST(B129, $C$124:$C$128, $B$124:$B$128)
NonLinear Estimates
So far our examination of trends has revolved around the use of linear estimates and the Excel functions that support that.
But as we saw above there are lots of cases where nonlinear estimates are required.
This section will deal with the following estimate types.
 Polynomial – Approximating a Polynomial function, a.x^n + b.x^(n1) + c.x^(n2) + … + m = 0
 Power – Approximating a Power function, y = a.x^b
 Logarithmic – Approximating a Logarithmic line, y = b.ln(x) + a
 Exponential – Approximating an Exponential line, y = b.m^x
Luckily Excel has a number of function and some tools to assist us here as well.
Exponential Functions
Exponential functions are based around the formula y = b.m^x
Excel has one function specific to growth estimates and that is the Logest function.
As with Linest, Logest is an array function.
eg: =LOGEST(Known Y’s, Known X’s, Const, Stats)
=LOGEST(C6:C13, B6:B13, true, false) Ctrl Shift Enter
Const = True or omitted b parameter is calculated
False b is set to 1
Stats = True Return additional regression statistics in an array
False Return the m coefficient and const b
Alternatively the values can be retrieved from the Logest array function using the Index function
B = INDEX( LOGEST( C6:C13, B6:B13, True, False), 1)
X = INDEX( LOGEST(C6:C13, B6:B13, True, False), 2)
The use of the Index function negates the requirement to use an Array Entered formula.1
However Logest, is a tricky function as it actually just passes values to the Linest function!
So we can actually use the Linest function for doing nearly all of our Exponential, Logarithmic and Power function trends.
But you ask “Doesn’t Linest give us the parameters for a straight line?”
Absolutely.
To use Linest to analyse an Exponential function we need to unwrap it so to speak and that is done by taking the Log of the Y values prior to putting them into the Linest equation, like this:
Form: = LINEST( LN(Known Y Values), Known X Values)
eg: = LINEST( LN(C32:C39), B32:B39) Which is an array formula
or = INDEX( LINEST( LN(C32:C39), B32:B39), 1) as a normal formula
Now the tricky part is that the m component or array parameter 2 must now be converted back to an exponential so we can use exp(m component) or =EXP( INDEX( LINEST( LN(C32:C39), B32:B39),2))
This is difficult to explain but is shown in a worked example on the Exponential Functions section of the Nonlinear Functions page of the example workbook attached.
Growth
The Growth function can be used to calculate an exponential curve that best fits your data based on a number of known X & Y values.
Form: = LINEST(Known Y Values, Known X Values, New X Values)
eg: = GROWTH($C$32:$C$39, $B$32:$B$39, B40) as a normal formula
This is also shown in a worked example on the Exponential Functions section of the Nonlinear Functions page of the example workbook attached.
Logarithmic Functions
Logarithmic functions are based around the formula y = b.LN(x)+a
Excel doesn’t have a specific function dealing with Logarithmic functions, however we can use the Linest function as previously described by first converting the data from a Logarithmic to Straight line and this is done by talking the LN of the X values.
Form: = LINEST( Known Y Values, LN(Known X Values))
eg: = LINEST( LN(C32:C39), B32:B39) as an array formula
or = INDEX( LINEST( LN(C32:C39), B32:B39), 1) as a normal formula
This is shown in a worked example on the Logarithmic Functions section of the Nonlinear Functions page of the example workbook attached.
Power Functions
Power functions are based around the formula y = a.x^b
Excel doesn’t have a specific function dealing with Power functions, however we can again use the Linest function as previously described by first converting the data from a Power function to Straight line and this is done by talking the LN of the X and Y values.
Form: =LINEST( LN(Known Y Values), LN(Known X Values))
eg: =LINEST( LN(C58:C65), LN(B58:B65)) as an array formula
or =INDEX( LINEST( LN(C58:C65), LN(B58:B65)), 1) as a normal formula
The above equations return Parameter 1 as b and Parameter 2 as LN(a)
LN(a) must be converted back to Parameter a by taking the Exp(a)
This is shown in a worked example on the Power Functions section of the Nonlinear Functions page of the example workbook attached.
Polynomial Functions
Polynomial functions are based around the formula y = a.x^n + b.x^(n1) + c.x^(n2) + … + m
Which typically looks like y = a.x^5 + b.x^4 + c.x^3 + d.x^2 + e.x +m
And if any of the parameters a to m are zero that part of the function will be zero and not shown.
Excel does have a specific function dealing with Polynomial functions, and you guessed it, it is the Linest function. The Linest function must be told that it is dealing with a polynomial function and this is done by adding another parameter to it’s input. The extra parameter is added by raising the know X values to the power of an array of number 1..n, where n is the power of the polynomial you want to use.
Form: = LINEST( Known Y Values, Known X Values^{1,2,3,..n})
eg: for a polynomial of power 3
= LINEST(C84:C94, B84:B94^{1,2,3}) as an array formula
or =INDEX( LINEST(C84:C94, B84:B94^{1,2,3}), 1) as a normal formula
The above equations return Parameter 1 as a, Parameter 2 as b, Parameter 3 as c if a power 3 polynomial is used.
This is shown in a worked example on the Power Functions section of the Nonlinear Functions page of the example workbook attached.
Multiple Variable Linear Regressions
The Linest function is able to be used to determine the regressions of multiple input variables (X1, X2, … Xn) that may contribute to a single output variable (Y).
This is best demonstrated with a simple example:
Hui’s Fruit Shop
Say we have a Fruit Shop and we only sell Apples & Oranges and we know how many Staff and what our Overhead Costs were and how much Profit we have made each year for the past decade.
This could be tabulated below:
We can use Linest to work out a regression for this model. That is what is the relationship between the output and all the inputs.
The format of this will be
Form: = LINEST(Known Y values, Known X Values, TRUE, TRUE) as an Array Formula
eg: = LINEST(E122:E132, A122:D132, TRUE, TRUE)
Note that the Known X Values of this example is a 4 column wide area representing the 4 variables.
This must be array entered in an area Xn + 1 columns wide and 5 rows deep, in our case a 5 column x 5 row area.
Note that the equation for then profit is made up of the array values from the first row of the answer array in reverse order
Y = 18.84.X1 + 27.98.X2 + 3851.79.X3 0.26.X4 15406.84
And that the parameters are in highest X4 to lowest X1 order followed by b at the end
You can also see the other parameters of the array of which the most important is the r2 factor which in this example is 0.90 indicating that there is a good fit between the Inputs and the Profit. Hence we could be relatively comfortable using our profit equation for the estimate of future profits.
Measuring the accuracy of the Estimation.
In the linear Linest section at the start and in the previous example we briefly mentioned a measure called the r2 parameter and said that because it had a value of 0.90 we would be comfortable using our estimation parameters to estimate future profits.
r2 is a measure of the error between the data points and the estimated values.
Its values vary between 0 = no relationship and 1 = a perfect relationship.
For example here are 3 charts based on the equation of Y = 3 X + 5
The equations of the lines of best fit and the r2 values are shown on each chart.
You can see that the data of Chart Y1 has a very close fit to the equation both visually and through a very high r2 value of 0.9962, where as at Y3 there is a very loose relationship between the data and the estimate which is shown visually as well as a low r2 value of 0.2552.
The derivation and use of this is beyond this post and I would refer you to the Excel Help of the Linest function, where it is discussed or Wikipedia.
How Does All This Work ?
The Excel Linest, Logest and Growth Functions all use a technique called “Least Squares Approximation”.
This is an iterative process which minimises the sum of the square of the distance from the estimated line to the actual data for all known data points. Once this is minimised the parameters which define the estimated line are returned to the user.
The scope of how Least Squares works is beyond the scope of this post, but if you are interested have a read at Wikipedia.
There are a number of other estimation techniques available which excel doesn’t support.
One should never assume that “just because Excel gave me the answer – it is correct” and this applies to the use of statistics more than any other area in maths or Excel usage.
Limitations:
The above techniques need to be used with a degree of caution.
Often a trend will exactly mathematically fit the data but in reality you wouldn’t use the equations.
In the picture below (courtesy of Wikipedia) 10 data points are exactly matched by a Polynomial function , whereas the linear estimate misses every point.
Which estimate would you choose to use? The linear function I hope.
This is discussed in more detail at Wikipedia.
Disclaimer
It should be noted that just because Excel returns an estimated line of best fit to your data, it doesn’t mean that your data actually follows that trend, it just may be coincidental and that user discretion is advised in all cases, refer Limitations above.
There are a number of other estimation techniques available and users interested should discuss these if required with a person expert in their data distribution.
Excel Functions Referred to in this Post
Exp – Return the exponential value of the input
Forecast – Forecast intermediate or future values based on known X and Y values
Growth – Derive an exponential estimate for a known set of X & Y values
Index – Lookup a value at row/column intercept from a table or array of data
Intercept – Return the intercept of a linear estimate
Linest – Derive a linear estimate for a known set of X & Y values
LN – Return the Natural Log value of the input
Logest – Derive an exponential estimate for a known set of X & Y values
Power – Returns the value of a number raised to a power
Slope – Return the slope of a linear estimate
Trend – Forecast intermediate or future values based on known X and Y values
Further Readings
Excel has a number of extra Statistical functions hidden in the Data Analysis addin.
I have not discussed or used these tools here as not all users will have access to them and the post is getting longish already.
Functions you may want to have a look at include:
Correl & Pearson: Both functions allow the calculation of correlation coefficients between variables.
Exponential Smoothing: The Exponential Smoothing analysis tool predicts a value that is based on the forecast for the prior period, adjusted for the error in that prior forecast
Fourier Analysis: The Fourier Analysis tool solves problems in linear systems and analyzes periodic data by using the Fast Fourier Transform (FFT) method to transform data, great for analysing periodic and frequency based data.
I would direct readers who are interested in using these techniques to look at the following sources
Microsoft Excel Help – Statistical Functions
Newton Excel Bach, not (just) an Excel Blog
Further Readings
What’s Next ?
In the next post we will looks at some Tools that Excel has to assist us in quickly determining which estimate method we can use.
I will also give you a neat little UDF to assist in your interpolations/extrapolations of your data which was used to make the animated GIF at the top of the first post.
ps: Happy Australia Day Everyone 🙂 !
Do you want to be awesome in Excel?
Here is a smart way to become awesome in Excel. Just signup for my Excel newsletter. Every week you will receive an Excel tip, tutorial, template or example delivered to your inbox. What more, as a joining bonus, I am giving away a 25 page eBook containing 95 Excel tips & tricks. Please signup below:
Your email address is safe with us. Our policies
 

Leave a Reply
Worksheet Properties via a Menu  Are You Trendy ? (Part 3) 
49 Responses to “Are You Trendy? (Part 2)”
[...] This post was mentioned on Twitter by Chandoo.org and Stray__Cat, Excel Insider. Excel Insider said: Are You Trendy? (Part 2): Forecasting using Excel Functions “Todays forecast will be Hot and Humid with a Chance... http://bit.ly/dX8kBX [...]
Nice second part but I've notice some kind of cut/paste between TREND and FORECAST which disturb my understanding of the differences between them.
Can you explain a bit more how they differ ?
@Cyril
They give the same results but ask for input in a slightly different way.
Forecast is useful for a single point
Trend is an array function and can do 1 point as a normal function or a number of points as an array function
Dear Sir,
I have a dataset which I believe shows exponential growth. I am trying to make a formula to know y when I know x. I did not study statistics (I studied law) so I am having a hard time figuring it out. Could I ask you to take a look at it and send you the dataset?
Thank you for your consideration.
Kind regards,
George
@George
Can you please ask the question in the chandoo.org Forums
http://forum.chandoo.org/
Please attach a sample file to give a more targetted answer
again, a wonderful post. quick questions: are you suggesting that r2 be the only evaluative statistic on model form and goodness of fit? what statistic could/should be used to compare "goodness of fit" across all linear and nonlinear regression model forms? how can it be derived from the Excel formulas?
I've used about 27 difference variations of the linear model transforming X and/or Y with square root, squared, natural log, and reciprocal. This includes the linear model and two models for when both X and Y have values between 0 and 1. These two models are the logistic and log probit models.
Hi Hui, great post...very timely as I was just scratching my head yesterday trying to work out the growth function. I have a question: one of the data models I was working on was corrupted (not by excel, but by its lesser equivilent, a human being) so I need to estimate a set of data points for December 2010. The data in question was definiately not linear, and has no clear trend, upward or downward. I'm thinking some type of average would be the best way to go (I have a couple of years worth of data), what would be your thoughts?
Prem
from EHA
Excellent tutorial. I must say that not many are aware of these features in EXCEL  how they added from pure Pivot Tables to all the way in Data Analysis and array functions all types  Stats, Math, Date and many more. Thank you, I am intimately familiar what you presented here and have used at one time or another...
Thanks, may be MS will give you some credit for advancing their tools beyond their own publicity.
Keep it coming
Wonderful Post. Man, I'm going to bookmark it for later reference.
@bill. r2 is very good only if the outcome has certain randomness (think stock price). But like Hui has shown in the last chart. An polynomial chart hitting almost all the dots like that may have r2 = .99 or so, given the known x and y. but the straight line which miss all the points may have a r2 = .96 or .95 So in that case I "eyeball" them and make a human judgement as to which to use. Right, Hui?
[...] For more details on the individual Tren Types refer to Are You Trendy (Part 2). [...]
[...] http://chandoo.org/wp/2011/01/26/trendlinesandforecastinginexcelpart2/ [...]
[...] Are You Trendy (Part 2) [...]
[...] http://chandoo.org/wp/2011/01/26/trendlinesandforecastinginexcelpart2/ [...]
Chandoo: Googled 2 days for information related to logarithmic trends, including Microsoft sites and Excel forums.
None of them had a comprehensible, complete, graphical, professional explanation that match your level.
Congratulations!
[...] be a part of your forecasting system. Now, let's have an overview of the FORECAST function in Excel.Let's begin by saying that FORECAST function in Microsoft Excel is not a complete inventory forecast...el. WordPress › [...]
[...] Trend lines & Forecasting in Excel – Part 2 [...]
[...] A useful post I found about Logest. The handiest tip I found is to use the “Index” function when using LOGEST, which makes it not necessary to enter it as an array formula. [...]
Excellent. Thank you...
Thanks so much for this great resource. I do have a question though. In theory, I would think that Slope and Trend are both trying to get to the same thing, right? Aren't they both based on finding the line of best fit? If so, I would think that the following might be true (although it isn't):
Let's assume a time series (monthly data) of rates and I am trying to predict the next value in that series (think extending a forward curve). I would think that if I took the Slope of the series and added it to the last number in the series, I should get a reasonable 'forecast' for the next month. However, this doesn't always match the number that TREND or FORECAST predict. I think if you have a perfect R^2 then perhaps it matches. Not sure. But in most real situations it doesn't match.
Is this because [SLOPE + Last Data Point] doesn't take into account the volatility or 'tightness' of the line of best fit, and TREND / FORECAST do? Very interested in any suggestions.
@Tracy
Your totally correct about the r2 value and its impact on adding the slope to the last value for linear functions
If r2=1.0 then the last value will be on the average slope line and adding the slope will give you the next Y value for the next X Value
There is a small but important caveat here.
Dates, especially Monthly data is not linear on the X Axis, because some months have 28, 29, 30 or 31 days
so you do need to be careful when assuming this applies to Date based data.
It sort of depends is the data based on say an accumulation of data from the month in which case the no of days is important or is the Month just a name for the X Axis and the data is independent of the days per month.
Thank you for your quick response. I understand about the daily/monthly issue. In my case, I am looking at a monthly rate so the amount of days in a month doesn't matter, but the point is well taken.
So just to confirm, Trend & Forecast use the variance in the R^2 to inject additional volatility (based on passed behavior) back into the future forecast which moves it off of (but within the overall regression and continuing to maintain the line of best fit) the perfect slope line? If so, I think I am on the same page.
And a Logarithmic forecast would basically be the inverse of a Growth forecast, right? Based on the shape of the curve?
Thanks a ton!
dear sir .... how to calculate a,b, and c from Y=ax+by+c using excel 2007.
i know Y ,x,and y values but i need a,b ,c sd, R values
@Manjunathan
Do you mean Y = aX + c or equivalent ?
It would be ideal if you could post a sample file of data so we can review
Refer: http://chandoo.org/forums/topic/postingasampleworkbook
BIG THanks pal..
It's very useful for me... Keep posting
[...] http://chandoo.org/wp/2011/01/26/trendlinesandforecastinginexcelpart2/ [...]
I need to use WinterHolt's model of forecasting in Excel. If there is any user defined function, it would be of great help to me.
@Satya
Have you had a look at some of the YouTube vids on exactly this topic: http://www.youtube.com/watch?v=VxwHMMgluOg
Hi Hui, great work, this was very helpful!
However, the link for downloading the Trend2.xls file seems to be broken. I was able to download the Trend1 and Trend 3 files successfully though. Can you repost it or email it to me?
Thank you!
@David
The link is fine?
I will email you
Chandoo  This is awesome; great example workbook too! I never leave your site without knowing a little bit more about excel and what it can do. Thanks a ton.
Tim
Chandoo,
Looking at your formulas for the Logarithmic function on the example workbook and trying to recreate for a project I'm working on. I'm curious, for your a and b you have the exact same formula, but with two different results (cells B19 and C19). Can explain how these two are working?
Thanks!
Mike
@Mike
B19 and C19 have the same Formula as they are array entered
What this means is that the formula =LINEST(C6:C13,LN(B6:B13))
Returns an array
Linest actually can return up to a 5 x 5 array of results
But for this function on the 2 positions are required.
The Linest Function returns the b and a parameters from the equation y = b.ln(x) + a
There is a list of the Linest()n array results at:
http://office.microsoft.com/enau/excelhelp/linestHP005209155.aspx
and
http://www.excelfunctions.net/ExcelLinestFunction.html
Hello!
I am doing commodity price forecasting, and I have data of daily prices from April 2012 April 2014.
The problem i'm having is (1) There are some daily data values missing, and (2) after I have dealt with the missing values (using smoothing), i have a problem calculating the seasonal indices because there are different numbers of working days in each month. some months have 23 days, some 21, some 20. because of the differing number of days, I cannot successfully calculate the seasonal effect. how do i tackle this problem?
P.S. I'm fairly new to excel, so please be gentle 🙂
@Mubin
Can you post your data?
I'd suggest asking the question in the Chandoo.org Forums
http://chandoo.org/forum/
Ill post on the forum too, thanks a lot
WeekDayNo.Day Date Wheat Prices/100kg (PKR)
1 1 Monday 02Apr12 2625.00
2 Tuesday 03Apr12 2625.00
3 Wednesday04Apr12 2625.00
4 Thursday 05Apr12 2625.00
5 Friday 06Apr12 2625.00
2 6 Monday 09Apr12 2625.00
7 Tuesday 10Apr12 2625.00
8 Wednesday 11Apr12 2625.00
9 Thursday 12Apr12 2625.00
10 Friday 13Apr12 2625.00
3 11 Monday 16Apr12 2625.00
12 Tuesday 17Apr12 2625.00
13 Wednesday 18Apr12 2625.00
14 Thursday 19Apr12 2625.00
15 Friday 20Apr12 2625.00
4 16 Monday 23Apr12 2625.00
17 Tuesday 24Apr12 2625.00
18 Wednesday 25Apr12 2625.00
19 Thursday 26Apr12 2625.00
20 Friday 27Apr12 2625.00
5 21 Monday 30Apr12 2625.00
22 Tuesday 01May12 2625.00
23 Wednesday 02May12 2625.00
24 Thursday 03May12 2625.00
25 Friday 04May12 2600.00
6 26 Monday 07May12 2600.00
27 Tuesday 08May12 2600.00
28 Wednesday 09May12 2588.00
29 Thursday 10May12 2588.00
30 Friday 11May12 2588.00
7 31 Monday 14May12 2575.00
32 Tuesday 15May12 2588.00
33 Wednesday 16May12 2588.00
34 Thursday 17May12 2588.00
35 Friday 18May12 2600.00
8 36 Monday 21May12 2613.00
37 Tuesday 22May12 2613.00
38 Wednesday 23May12 2613.00
39 Thursday 24May12 2569.00
40 Friday 25May12 2568.00
9 41 Monday 28May12 2588.00
42 Tuesday 29May12 2598.00
43 Wednesday 30May12 2588.00
44 Thursday 31May12 2588.00
45 Friday 01Jun12 2588.00
10 46 Monday 04Jun12 2588.00
47 Tuesday 05Jun12 2588.00
48 Wednesday 06Jun12 2588.00
49 Thursday 07Jun12 2588.00
50 Friday 08Jun12 2588.00
11 51 Monday 11Jun12 2588.00
52 Tuesday 12Jun12 2588.00
53 Wednesday 13Jun12 2588.00
54 Thursday 14Jun12 2588.00
55 Friday 15Jun12 2588.00
12 56 Monday 18Jun12 2588.00
57 Tuesday 19Jun12 2588.00
58 Wednesday 20Jun12 2588.00
59 Thursday 21Jun12 2588.00
60 Friday 22Jun12 2588.00
13 61 Monday 25Jun12 2588.00
62 Tuesday 26Jun12 2588.00
63 Wednesday 27Jun12 2588.00
64 Thursday 28Jun12 2588.00
65 Friday 29Jun12 2588.00
14 66 Monday 02Jul12 2588.00
67 Tuesday 03Jul12 2588.00
68 Wednesday 04Jul12 2588.00
69 Thursday 05Jul12 2588.00
70 Friday 06Jul12 2588.00
15 71 Monday 09Jul12 2588.00
72 Tuesday 10Jul12 2588.00
73 Wednesday 11Jul12 2588.00
74 Thursday 12Jul12 2588.00
75 Friday 13Jul12 2588.00
16 76 Monday 16Jul12 2588.00
77 Tuesday 17Jul12 2588.00
78 Wednesday 18Jul12 2588.00
79 Thursday 19Jul12 2588.00
80 Friday 20Jul12 2588.00
17 81 Monday 23Jul12 2588.00
82 Tuesday 24Jul12 2588.00
83 Wednesday 25Jul12 2588.00
84 Thursday 26Jul12 2588.00
85 Friday 27Jul12 2588.00
18 86 Monday 30Jul12 2588.00
87 Tuesday 31Jul12 2588.00
88 Wednesday 01Aug12 2588.00
89 Thursday 02Aug12 2588.00
90 Friday 03Aug12 2588.00
19 91 Monday 06Aug12 2588.00
92 Tuesday 07Aug12 2588.00
93 Wednesday 08Aug12 2588.00
94 Thursday 09Aug12 2588.00
95 Friday 10Aug12 2588.00
20 96 Monday 13Aug12 2600.00
97 Tuesday 14Aug12 2600.00
98 Wednesday 15Aug12 2600.00
99 Thursday 16Aug12 2600.00
100 Friday 17Aug12 2600.00
21 101 Monday 20Aug12 2600.00
102 Tuesday 21Aug12 2600.00
103 Wednesday 22Aug12 2600.00
104 Thursday 23Aug12 2600.00
105 Friday 24Aug12 2600.00
22 106 Monday 27Aug12 2600.00
107 Tuesday 28Aug12 2600.00
108 Wednesday 29Aug12 2600.00
109 Thursday 30Aug12 2600.00
110 Friday 31Aug12 2600.00
23 111 Monday 03Sep12 2600.00
112 Tuesday 04Sep12 2600.00
113 Wednesday 05Sep12 2600.00
114 Thursday 06Sep12 2575.00
115 Friday 07Sep12 2575.00
24 116 Monday 10Sep12 2575.00
117 Tuesday 11Sep12 2575.00
118 Wednesday 12Sep12 2575.00
119 Thursday 13Sep12 2575.00
120 Friday 14Sep12 2575.00
25 121 Monday 17Sep12 2575.00
122 Tuesday 18Sep12 2575.00
123 Wednesday 19Sep12 2575.00
124 Thursday 20Sep12 2575.00
125 Friday 21Sep12 2575.00
26 126 Monday 24Sep12 2575.00
127 Tuesday 25Sep12 2575.00
128 Wednesday 26Sep12 2575.00
129 Thursday 27Sep12 2575.00
130 Friday 28Sep12 2575.00
27 131 Monday 01Oct12 2575.00
132 Tuesday 02Oct12 2575.00
133 Wednesday 03Oct12 2575.00
134 Thursday 04Oct12 2575.00
135 Friday 05Oct12 2575.00
28 136 Monday 08Oct12 2575.00
137 Tuesday 09Oct12 2575.00
138 Wednesday 10Oct12 2575.00
139 Thursday 11Oct12 2575.00
140 Friday 12Oct12 2575.00
29 141 Monday 15Oct12 2575.00
142 Tuesday 16Oct12 2575.00
143 Wednesday 17Oct12 2575.00
144 Thursday 18Oct12 2575.00
145 Friday 19Oct12 2575.00
30 146 Monday 22Oct12 2838.00
147 Tuesday 23Oct12 2838.00
148 Wednesday 24Oct12 2838.00
149 Thursday 25Oct12 2838.00
150 Friday 26Oct12 2838.00
31 151 Monday 29Oct12 2838.00
152 Tuesday 30Oct12 2838.00
153 Wednesday 31Oct12 2838.00
154 Thursday 01Nov12 2838.00
155 Friday 02Nov12 2838.00
32 156 Monday 05Nov12 2838.00
157 Tuesday 06Nov12 2875.00
158 Wednesday 07Nov12 2875.00
159 Thursday 08Nov12 2875.00
160 Friday 09Nov12 2875.00
33 161 Monday 12Nov12 2875.00
162 Tuesday 13Nov12 2875.00
163 Wednesday 14Nov12 2875.00
164 Thursday 15Nov12 2875.00
165 Friday 16Nov12 2875.00
34 166 Monday 19Nov12 2875.00
167 Tuesday 20Nov12 2875.00
168 Wednesday 21Nov12 2875.00
169 Thursday 22Nov12 2875.00
170 Friday 23Nov12 3025.00
35 171 Monday 26Nov12 3025.00
172 Tuesday 27Nov12 3025.00
173 Wednesday 28Nov12 3025.00
174 Thursday 29Nov12 3025.00
175 Friday 30Nov12 3025.00
36 176 Monday 03Dec12 3025.00
177 Tuesday 04Dec12 3025.00
178 Wednesday 05Dec12 3025.00
179 Thursday 06Dec12 3025.00
180 Friday 07Dec12 3025.00
37 181 Monday 10Dec12 3025.00
182 Tuesday 11Dec12 3025.00
183 Wednesday 12Dec12 3025.00
184 Thursday 13Dec12 3025.00
185 Friday 14Dec12 3025.00
38 186 Monday 17Dec12 3025.00
187 Tuesday 18Dec12 3025.00
188 Wednesday 19Dec12 3025.00
189 Thursday 20Dec12 3025.00
190 Friday 21Dec12 3025.00
39 191 Monday 24Dec12 3075.00
192 Tuesday 25Dec12 3075.00
193 Wednesday 26Dec12 3075.00
194 Thursday 27Dec12 3075.00
195 Friday 28Dec12 3075.00
40 196 Monday 31Dec12 3075.00
197 Tuesday 01Jan13 3075.00
198 Wednesday 02Jan13 3075.00
199 Thursday 03Jan13 3075.00
200 Friday 04Jan13 3075.00
41 201 Monday 07Jan13 3075.00
202 Tuesday 08Jan13 3075.00
203 Wednesday 09Jan13 3075.00
204 Thursday 10Jan13 3075.00
205 Friday 11Jan13 3075.00
42 206 Monday 14Jan13 3075.00
207 Tuesday 15Jan13 3075.00
208 Wednesday 16Jan13 3075.00
209 Thursday 17Jan13 3075.00
210 Friday 18Jan13 3075.00
43 211 Monday 21Jan13 3075.00
212 Tuesday 22Jan13 3075.00
213 Wednesday 23Jan13 3075.00
214 Thursday 24Jan13 3075.00
215 Friday 25Jan13 3075.00
44 216 Monday 28Jan13 3075.00
217 Tuesday 29Jan13 3075.00
218 Wednesday 30Jan13 2995.20
219 Thursday 31Jan13 2997.68
220 Friday 01Feb13 3000.17
45 221 Monday 04Feb13 3002.65
222 Tuesday 05Feb13 3005.14
223 Wednesday 06Feb13 3007.62
224 Thursday 07Feb13 3010.11
225 Friday 08Feb13 3012.59
46 226 Monday 11Feb13 3015.08
227 Tuesday 12Feb13 3017.56
228 Wednesday 13Feb13 3020.05
229 Thursday 14Feb13 3022.53
230 Friday 15Feb13 3025.02
47 231 Monday 18Feb13 3027.51
232 Tuesday 19Feb13 3029.99
233 Wednesday 20Feb13 3032.48
234 Thursday 21Feb13 3034.96
235 Friday 22Feb13 3037.45
48 236 Monday 25Feb13 3039.93
237 Tuesday 26Feb13 3042.42
238 Wednesday 27Feb13 3044.90
239 Thursday 28Feb13 3047.39
240 Friday 01Mar13 3049.87
49 241 Monday 04Mar13 3052.36
242 Tuesday 05Mar13 3054.84
243 Wednesday 06Mar13 3057.33
244 Thursday 07Mar13 3059.81
245 Friday 08Mar13 3062.30
50 246 Monday 11Mar13 3064.79
247 Tuesday 12Mar13 3067.27
248 Wednesday 13Mar13 3069.76
249 Thursday 14Mar13 3072.24
250 Friday 15Mar13 3074.73
51 251 Monday 18Mar13 3077.21
252 Tuesday 19Mar13 3079.70
253 Wednesday 20Mar13 3082.18
254 Thursday 21Mar13 3084.67
255 Friday 22Mar13 3087.15
52 256 Monday 25Mar13 3089.64
257 Tuesday 26Mar13 3092.12
258 Wednesday 27Mar13 3094.61
259 Thursday 28Mar13 3097.09
260 Friday 29Mar13 3099.58
53 261 Monday 01Apr13 3102.07
262 Tuesday 02Apr13 3104.55
263 Wednesday 03Apr13 3107.04
264 Thursday 04Apr13 3109.52
265 Friday 05Apr13 3112.01
54 266 Monday 08Apr13 3114.49
267 Tuesday 09Apr13 3116.98
268 Wednesday 10Apr13 3119.46
269 Thursday 11Apr13 3121.95
270 Friday 12Apr13 3124.43
55 271 Monday 15Apr13 3126.92
272 Tuesday 16Apr13 3129.40
273 Wednesday 17Apr13 3131.89
274 Thursday 18Apr13 3134.37
275 Friday 19Apr13 3136.86
56 276 Monday 22Apr13 3139.35
277 Tuesday 23Apr13 3141.83
278 Wednesday 24Apr13 3144.32
279 Thursday 25Apr13 3146.80
280 Friday 26Apr13 2938.00
57 281 Monday 29Apr13 2938.00
282 Tuesday 30Apr13 2938.00
283 Wednesday 01May13 2938.00
284 Thursday 02May13 2938.00
285 Friday 03May13 2938.00
58 286 Monday 06May13 2938.00
287 Tuesday 07May13 2844.00
288 Wednesday 08May13 2844.00
289 Thursday 09May13 2844.00
290 Friday 10May13 2844.00
59 291 Monday 13May13 2844.00
292 Tuesday 14May13 2844.00
293 Wednesday 15May13 2844.00
294 Thursday 16May13 2844.00
295 Friday 17May13 2844.00
60 296 Monday 20May13 2844.00
297 Tuesday 21May13 2844.00
298 Wednesday 22May13 2844.00
299 Thursday 23May13 2844.00
300 Friday 24May13 2844.00
61 301 Monday 27May13 2844.00
302 Tuesday 28May13 2988.00
303 Wednesday 29May13 3031.00
304 Thursday 30May13 3031.00
305 Friday 31May13 3031.00
62 306 Monday 03Jun13 3031.00
307 Tuesday 04Jun13 2975.00
308 Wednesday 05Jun13 2975.00
309 Thursday 06Jun13 2975.00
310 Friday 07Jun13 2975.00
63 311 Monday 10Jun13 2975.00
312 Tuesday 11Jun13 2975.00
313 Wednesday 12Jun13 3013.00
314 Thursday 13Jun13 3013.00
315 Friday 14Jun13 3157.00
64 316 Monday 17Jun13 3157.00
317 Tuesday 18Jun13 3157.00
318 Wednesday 19Jun13 3157.00
319 Thursday 20Jun13 3157.00
320 Friday 21Jun13 3157.00
65 321 Monday 24Jun13 3157.00
322 Tuesday 25Jun13 3157.00
323 Wednesday 26Jun13 3275.00
324 Thursday 27Jun13 3288.00
325 Friday 28Jun13 3338.00
66 326 Monday 01Jul13 3338.00
327 Tuesday 02Jul13 3338.00
328 Wednesday 03Jul13 3338.00
329 Thursday 04Jul13 3338.00
330 Friday 05Jul13 3338.00
67 331 Monday 08Jul13 3338.00
332 Tuesday 09Jul13 3338.00
333 Wednesday 10Jul13 3338.00
334 Thursday 11Jul13 3338.00
335 Friday 12Jul13 3338.00
68 336 Monday 15Jul13 3363.00
337 Tuesday 16Jul13 3363.00
338 Wednesday 17Jul13 3363.00
339 Thursday 18Jul13 3363.00
340 Friday 19Jul13 3363.00
69 341 Monday 22Jul13 3363.00
342 Tuesday 23Jul13 3363.00
343 Wednesday 24Jul13 3363.00
344 Thursday 25Jul13 3363.00
345 Friday 26Jul13 3363.00
70 346 Monday 29Jul13 3363.00
347 Tuesday 30Jul13 3363.00
348 Wednesday 31Jul13 3363.00
349 Thursday 01Aug13 3363.00
350 Friday 02Aug13 3363.00
71 351 Monday 05Aug13 3363.00
352 Tuesday 06Aug13 3363.00
353 Wednesday 07Aug13 3363.00
354 Thursday 08Aug13 3363.00
355 Friday 09Aug13 3363.00
72 356 Monday 12Aug13 3363.00
357 Tuesday 13Aug13 3363.00
358 Wednesday 14Aug13 3363.00
359 Thursday 15Aug13 3363.00
360 Friday 16Aug13 3588.00
73 361 Monday 19Aug13 3588.00
362 Tuesday 20Aug13 3588.00
363 Wednesday 21Aug13 3588.00
364 Thursday 22Aug13 3588.00
365 Friday 23Aug13 3588.00
74 366 Monday 26Aug13 3488.00
367 Tuesday 27Aug13 3488.00
368 Wednesday 28Aug13 3488.00
369 Thursday 29Aug13 3488.00
370 Friday 30Aug13 3488.00
75 371 Monday 02Sep13 3635.00
372 Tuesday 03Sep13 3635.00
373 Wednesday 04Sep13 3635.00
374 Thursday 05Sep13 3635.00
375 Friday 06Sep13 3635.00
76 376 Monday 09Sep13 3635.00
377 Tuesday 10Sep13 3650.00
378 Wednesday 11Sep13 3638.00
379 Thursday 12Sep13 3635.00
380 Friday 13Sep13 3635.00
77 381 Monday 16Sep13 3650.00
382 Tuesday 17Sep13 3635.00
383 Wednesday 18Sep13 3635.00
384 Thursday 19Sep13 3635.00
385 Friday 20Sep13 3635.00
78 386 Monday 23Sep13 3686.00
387 Tuesday 24Sep13 3635.00
388 Wednesday 25Sep13 3594.00
389 Thursday 26Sep13 3944.00
390 Friday 27Sep13 3635.00
79 391 Monday 30Sep13 3594.00
392 Tuesday 01Oct13 3638.00
393 Wednesday 02Oct13 3656.00
394 Thursday 03Oct13 3656.00
395 Friday 04Oct13 3656.00
80 396 Monday 07Oct13 3656.00
397 Tuesday 08Oct13 3656.00
398 Wednesday 09Oct13 3656.00
399 Thursday 10Oct13 3656.00
400 Friday 11Oct13 3656.00
81 401 Monday 14Oct13 3656.00
402 Tuesday 15Oct13 3675.00
403 Wednesday 16Oct13 3675.00
404 Thursday 17Oct13 3675.00
405 Friday 18Oct13 3675.00
82 406 Monday 21Oct13 3675.00
407 Tuesday 22Oct13 3675.00
408 Wednesday 23Oct13 3675.00
409 Thursday 24Oct13 3675.00
410 Friday 25Oct13 3675.00
83 411 Monday 28Oct13 3725.00
412 Tuesday 29Oct13 3725.00
413 Wednesday 30Oct13 3725.00
414 Thursday 31Oct13 3725.00
415 Friday 01Nov13 3738.00
84 416 Monday 04Nov13 3763.00
417 Tuesday 05Nov13 3763.00
418 Wednesday 06Nov13 3675.00
419 Thursday 07Nov13 3675.00
420 Friday 08Nov13 3675.00
85 421 Monday 11Nov13 3853.00
422 Tuesday 12Nov13 3550.00
423 Wednesday 13Nov13 3788.00
424 Thursday 14Nov13 3853.00
425 Friday 15Nov13 3853.00
86 426 Monday 18Nov13 3925.00
427 Tuesday 19Nov13 3925.00
428 Wednesday 20Nov13 3925.00
429 Thursday 21Nov13 4025.00
430 Friday 22Nov13 3925.00
87 431 Monday 25Nov13 4013.00
432 Tuesday 26Nov13 4013.00
433 Wednesday 27Nov13 4013.00
434 Thursday 28Nov13 4013.00
435 Friday 29Nov13 4013.00
88 436 Monday 02Dec13 4013.00
437 Tuesday 03Dec13 4013.00
438 Wednesday 04Dec13 3200.00
439 Thursday 05Dec13 3943.00
440 Friday 06Dec13 3943.00
89 441 Monday 09Dec13 3988.00
442 Tuesday 10Dec13 3988.00
443 Wednesday 11Dec13 3988.00
444 Thursday 12Dec13 3988.00
445 Friday 13Dec13 3988.00
90 446 Monday 16Dec13 3988.00
447 Tuesday 17Dec13 3988.00
448 Wednesday 18Dec13 3988.00
449 Thursday 19Dec13 3938.00
450 Friday 20Dec13 3988.00
91 451 Monday 23Dec13 3988.00
452 Tuesday 24Dec13 3943.00
453 Wednesday 25Dec13 3943.00
454 Thursday 26Dec13 3988.00
455 Friday 27Dec13 3988.00
92 456 Monday 30Dec13 3988.00
457 Tuesday 31Dec13 3988.00
458 Wednesday 01Jan14 3988.00
459 Thursday 02Jan14 3988.00
460 Friday 03Jan14 3988.00
93 461 Monday 06Jan14 3988.00
462 Tuesday 07Jan14 3982.00
463 Wednesday 08Jan14 3794.67
464 Thursday 09Jan14 3988.00
465 Friday 10Jan14 3975.00
94 466 Monday 13Jan14 3975.00
467 Tuesday 14Jan14 3807.09
468 Wednesday 15Jan14 3988.00
469 Thursday 16Jan14 3975.00
470 Friday 17Jan14 3975.00
95 471 Monday 20Jan14 3975.00
472 Tuesday 21Jan14 3988.00
473 Wednesday 22Jan14 3988.00
474 Thursday 23Jan14 3988.00
475 Friday 24Jan14 3988.00
96 476 Monday 27Jan14 3988.00
477 Tuesday 28Jan14 3988.00
478 Wednesday 29Jan14 3858.80
479 Thursday 30Jan14 3861.97
480 Friday 31Jan14 3865.13
97 481 Monday 03Feb14 3888.00
482 Tuesday 04Feb14 3888.00
483 Wednesday 05Feb14 3888.00
484 Thursday 06Feb14 3888.00
485 Friday 07Feb14 3888.00
98 486 Monday 10Feb14 3888.00
487 Tuesday 11Feb14 3888.00
488 Wednesday 12Feb14 3888.00
489 Thursday 13Feb14 3888.00
490 Friday 14Feb14 3888.00
99 491 Monday 17Feb14 3900.34
492 Tuesday 18Feb14 3903.50
493 Wednesday 19Feb14 3906.66
494 Thursday 20Feb14 3909.83
495 Friday 21Feb14 3913.00
100 496 Monday 24Feb14 3913.00
497 Tuesday 25Feb14 3913.00
498 Wednesday 26Feb14 3913.00
499 Thursday 27Feb14 3913.00
500 Friday 28Feb14 3913.00
101 501 Monday 03Mar14 3888.00
502 Tuesday 04Mar14 3888.00
503 Wednesday 05Mar14 3888.00
504 Thursday 06Mar14 3888.00
505 Friday 07Mar14 3888.00
102 506 Monday 10Mar14 3888.00
507 Tuesday 11Mar14 3888.00
508 Wednesday 12Mar14 3888.00
509 Thursday 13Mar14 3888.00
510 Friday 14Mar14 3888.00
103 511 Monday 17Mar14 3888.00
512 Tuesday 18Mar14 3888.00
513 Wednesday 19Mar14 3941.00
514 Thursday 20Mar14 3041.00
515 Friday 21Mar14 3962.80
104 516 Monday 24Mar14 3965.93
517 Tuesday 25Mar14 3969.05
518 Wednesday 26Mar14 3972.17
519 Thursday 27Mar14 3975.30
520 Friday 28Mar14 3978.42
105 521 Monday 31Mar14 3981.54
@Mubin
Did you post on the Forums as I haven't seen it?
I have just posted on the forum. Thanks for the help, and please see my thread!
[…] If you want to learn about how to do simple forecasting and trend analysis, please see the official forecast function in Excel post on the Microsoft website, and this handy tutorial on trend lines and forecasting in excel. […]
[…] If you want to learn about how to do simple forecasting and trend analysis, please see the official forecast function in Excel post on the Microsoft website, and this handy tutorial on trend lines and forecasting in excel. […]
[…] If you want to learn about how to do simple forecasting and trend analysis, please see the official forecast function in Excel post on the Microsoft website, and this handy tutorial on trend lines and forecasting in excel. […]
[…] If you want to learn about how to do simple forecasting and trend analysis, please see the official forecast function in Excel post on the Microsoft website, and this handy tutorial on trend lines and forecasting in excel. […]
[…] In dit artikel staat een praktische uitleg hoe je hiermee kunt […]
hi, thanks for this page. a lot of nice examples. spent a very long day(s) finding another item example that i still need to incorporate into my sheet.. but i think you might add an example for: PERCENTILE
i needed an exact relative position between two points, for varying price & volume.
what currently using is a sumproduct(match(volume)match(price) gets a required level of volume > than minimum level. the problem was (still is until i figure out how to combine these), is needing the varied levels inbetween my volume  price levels / too big of gaps to get a good reading will be fixed by:
=PERCENTILE($AB$1:$AB$18,1PERCENTRANK($AE$1:$AE$18,FT783))
(col AE ascending volume levels 10k to 100M, col AE desc price levels $10 to 0.0001), gets the exact volume level needed between my settings..
the formula in use for volume as stated, for: sumproduct( match(volume  match(price is below, but combining these 2 will get the best of bothe worlds..
sorry don't have that yet to give, but:
=IF(OR(B9=0,FC9={"",0},FC9$AB$18,20,MATCH(FC9,$AB$1:$AB$18,1)))O9),0))
O9 being the price half of that:
=IF(B9>0,IF(FT9>$AE$1,0,MATCH(FT9,$AE$1:$AE$18,1)),0)
does all that make sense ? 🙂
follow up comments means, >>>> (to my post) ??
STILL A PROBLEM: (site dropping my eg) see if this works.. 2nd formula from bottom should say:
IF(OR(B9=0,FC9={"",0},FC9$AB$18,20,
MATCH (FC9,$AB$1:$AB$18,1)))O9),0))
CORRECTION:
ouch.. i pasted right from from a cell, don't know how sumproduct terms etc dropped out of what posted, 2nd formula from bottom should read:
=IF(OR(B9=0,FC9={"",0},FC9$AB$18,20,MATCH(FC9,$AB$1:$AB$18,1)))O9),0))
it is dropping pieces out of the example trying to give, not sure what the deal is. one more time:
I F ( O R ( B9=0,
FC9={"",0},FC9$AB$18,20,
M A T C H ( F C 9,$AB$1:$AB$18,1)))O9),0))
@Dave
Use gt gte lt & lte instead of the >'s
too funny, 2nd formula from bottom is incorrect, site will not let me post it.
if this works should say: 0, max (sum product, if fcx>ab18,20, match( fc 9 etc
IF(OR(B9=0,FC9={"",0},FC9$AB$18,20,MATCH(FC9,$AB$1:$AB$18,1)))O9),0))