Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 585
  • Last Modified:

excel array formula to calculate min of two variable columns

I have the following data file where column B shows the data, column C shows the max of the data since inception and column D shows the percentage difference between columns C and D.  In cell E1, the minimum of column D is calculated. All straightforward stuff.

I would like to have an array formula that calculates the minimum difference over a set number of days (i.e. last 100 or 200 rows) and does away with columns C and D. So the formula would include lastrow (offset) - the number of rows.(variable).

I have included an example with spreadsheets.

Thanks,
CC
exampleArrayFile.xlsx
0
CC10
Asked:
CC10
  • 3
  • 3
  • 2
1 Solution
 
Saqib Husain, SyedEngineerCommented:
=MIN(OFFSET(B1,COUNT(C:C)-200,0,200)/OFFSET(C1,COUNT(C:C)-200,0,200)-1)

for the last 200
0
 
CC10Author Commented:
I get a Value# sign when I enter the formula

Also this does not solve my problem. The formula should work so that for any number of rows chosen, column C will be adjusted.
Column C calculates the maximum value of column B for the number of rows chosen.

Assume rows C and D do not exist. Then, if the number of rows chosen is 100, the formula must calculate MIN(max(last100 rows of columnB)/((last100rowsof columnsB)). I think this is some sort of array formula as each row as has to be calculated.

Does that make sense?
0
 
Saqib Husain, SyedEngineerCommented:
I get a Value# sign when I enter the formula
Enter it as an array formula

The formula should work so that for any number of rows chosen, column C will be adjusted
Change all 200's to a cell address and enter 200 or whatever in that cell.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
NBVCCommented:
Try this array formula:

=MIN(N(OFFSET(B:B,COUNTA(B:B)-200+ROW(INDIRECT("1:"&ROWS(1:200)))-1,,1,1))/SUBTOTAL(4,OFFSET(B:B,COUNTA(B:B)-200,,ROW(INDIRECT("1:"&ROWS(1:200))),1))-1)

for 200 last records.
0
 
CC10Author Commented:
Thanks, thats fine.
0
 
NBVCCommented:
Are you certain the solution you accepted gives you the expected results?

I tried it and whether you use 200 or replace the 200 with 100 you get the same result of -7.97%!

The formula I gave gives the results you show on ExampleLast200 given the full dataset of ExampleAll.

And if you replace all the 200's with 100's you get the result you show in ExampleLast100.
0
 
Saqib Husain, SyedEngineerCommented:
You are probably right. That formula works only on the 200 sheet. Not on the first sheet.
0
 
NBVCCommented:
CC10,

In case you decide that my formula was the what you were actually looking for; then in order to use a cell reference to contain the 200 or whatever number of rows you want to test for, try:

=MIN(N(OFFSET(B:B,COUNTA(B:B)-N1+ROW(INDIRECT("1:"&N1))-1,,1,1))/SUBTOTAL(4,OFFSET(B:B,COUNTA(B:B)-N1,,ROW(INDIRECT("1:"&N1)),1))-1)

where N1 contains the number of last rows to test for (e.g. 200)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now