Need help shortening formula

mrblister

Board Regular
Joined
Nov 20, 2016
Messages
191
Office Version
  1. 2019
Platform
  1. Windows
=C5+(IF(INDEX(ColumnD, 1)="apples",IF($B3<index(table1, match("fertilizernumber",="" table1rownames,0),="" match(index(columna,="" 1)&"apples",="" table1headers,0)),vlookup($b3,table2,match(index(columna,="" 1)&"apples",table2headers,0),true),seriessum($b3,1,1,indirect(vlookup(index(columna,="" 1),table3,match("coefs",table3headers,0))))),0)*lookup(index(columna,="" 1),$a$2:$z$2,$a5:$z5))+<="" td=""></index(table1,>
(IF(INDEX(ColumnD, 2)="apples",IF($B3<index(table1, match("fertilizernumber",="" table1rownames,0),="" match(index(columna,="" 2)&"apples",="" table1headers,0)),vlookup($b3,table2,match(index(columna,="" 2)&"apples",table2headers,0),true),seriessum($b3,1,1,indirect(vlookup(index(columna,="" 2),table3,match("coefs",table3headers,0))))),0)*lookup(index(columna,="" 2),$a$2:$z$2,$a5:$z5))+...<="" td=""></index(table1,>
(IF(INDEX(ColumnD, n)="apples",IF($B3<index(table1, match("fertilizernumber",="" table1rownames,0),="" match(index(columna,="" n)&"apples",="" table1headers,0)),vlookup($b3,table2,match(index(columna,="" n)&"apples",table2headers,0),true),seriessum($b3,1,1,indirect(vlookup(index(columna,="" n),table3,match("coefs",table3headers,0))))),0)*lookup(index(columna,="" n),$a$2:$z$2,$a5:$z5))<="" td=""></index(table1,>

<tbody>
</tbody>

EDITING!!! I DON'T KNOW WHY, BUT IT WON'T DISPLAY THE WHOLE FORMULA!

n = number of entries in ColumnD (the same number of entries in columnA)

The above formula is located in C6, copied down. It checks the each item (fruit type) in ColumnD, and if it matches "apples", the rest of the function is computed. If ColumnD is a match, the value of ColumnA (on the same row as ColumnD) is returned (like a left-lookup) as the identifier (fruit name e.g. "RoyalGala") used in the rest of the formula, as the ID used to searched for in the other tables. If it matches certain criteria, look up the output on Table2, otherwise compute the output using a formula.
Then after all the results are summed up, then add the previous amount from the cell above.

Any ideas on ways to shorten this? It works, but is super-long. I tried converting it to array and sumproduct but I must've made a mistake somewhere.
 
Last edited:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
looks like only a small part of your formula came through? If you have < or > leave a space around them
 
Upvote 0
looks like only a small part of your formula came through? If you have < or > leave a space around them

Yeah, I have no idea why this is happening. I don't have "<" or ">". Any other reasons why my formula won't display? I've tried several times, and in different ways.
 
Upvote 0
OK, so I can't edit my original post because the time limit has expired. :eek:

This is the original post... with the full formula.


<index(table1, match("fertilizernumber",="" table1rownames,0),="" match(index(columna,="" 1)&"apples",="" table1headers,0)),vlookup($b3,table2,match(index(columna,="" 1)&"apples",table2headers,0),true),seriessum($b3,1,1,indirect(vlookup(index(columna,="" 1),table3,match("coefs",table3headers,0))))),0)*lookup(index(columna,="" 1),$a$2:$z$2,$a5:$z5))+
<index(table1, match("fertilizernumber",="" table1rownames,0),="" match(index(columna,="" 2)&"apples",="" table1headers,0)),vlookup($b3,table2,match(index(columna,="" 2)&"apples",table2headers,0),true),seriessum($b3,1,1,indirect(vlookup(index(columna,="" 2),table3,match("coefs",table3headers,0))))),0)*lookup(index(columna,="" 2),$a$2:$z$2,$a5:$z5))+
<index(table1, match("fertilizernumber",="" table1rownames,0),="" match(index(columna,="" n)&"apples",="" table1headers,0)),vlookup($b3,table2,match(index(columna,="" n)&"apples",table2headers,0),true),seriessum($b3,1,1,indirect(vlookup(index(columna,="" n),table3,match("coefs",table3headers,0))))),0)*lookup(index(columna,="" n),$a$2:$z$2,$a5:$z5))
=C5+(
(IF(INDEX(ColumnD, 1)="apples",IF($B3 < INDEX(Table1, MATCH("FertilizerNumber", Table1RowNames,0), MATCH(INDEX(ColumnA, 1)&"apples", Table1Headers,0)),VLOOKUP($B3,Table2,MATCH(INDEX(ColumnA, 1)&"apples",Table2Headers,0),TRUE),SERIESSUM($B3,1,1,INDIRECT(VLOOKUP(INDEX(ColumnA, 1),Table3,MATCH("Coefs",Table3Headers,0))))),0)*LOOKUP(INDEX(ColumnA, 1),$A$2:$Z$2,$A5:$Z5))+

(IF(INDEX(ColumnD, 2)="apples",IF($B3 < INDEX(Table1, MATCH("FertilizerNumber", Table1RowNames,0), MATCH(INDEX(ColumnA, 2)&"apples", Table1Headers,0)),VLOOKUP($B3,Table2,MATCH(INDEX(ColumnA, 2)&"apples",Table2Headers,0),TRUE),SERIESSUM($B3,1,1,INDIRECT(VLOOKUP(INDEX(ColumnA, 2),Table3,MATCH("Coefs",Table3Headers,0))))),0)*LOOKUP(INDEX(ColumnA, 2),$A$2:$Z$2,$A5:$Z5))+

(IF(INDEX(ColumnD, n)="apples",IF($B3 < INDEX(Table1, MATCH("FertilizerNumber", Table1RowNames,0), MATCH(INDEX(ColumnA, n)&"apples", Table1Headers,0)),VLOOKUP($B3,Table2,MATCH(INDEX(ColumnA, n)&"apples",Table2Headers,0),TRUE),SERIESSUM($B3,1,1,INDIRECT(VLOOKUP(INDEX(ColumnA, n),Table3,MATCH("Coefs",Table3Headers,0))))),0)*LOOKUP(INDEX(ColumnA, n),$A$2:$Z$2,$A5:$Z5))
)

n = number of entries in ColumnD (the same number of entries in columnA)

The above formula is located in C6, copied down. It checks the each item (fruit type) in ColumnD, and if it matches "apples", the rest of the function is computed. If ColumnD is a match, the value of ColumnA (on the same row as ColumnD) is returned (like a left-lookup) as the identifier (fruit name e.g. "RoyalGala") used in the rest of the formula, as the ID used to searched for in the other tables. If it matches certain criteria, look up the output on Table2, otherwise compute the output using a formula.
Then after all the results are summed up, then add the previous amount from the cell above.

Any ideas on ways to shorten this? It works, but is super-long. I tried converting it to array and sumproduct but I must've made a mistake somewhere.</index(table1,></index(table1,></index(table1,>
 
Upvote 0
OK wow yes, that is some monster.

Hard to make out what all that is doing, but have you considered SUMIFS() for this?
 
Upvote 0
Looks like you can simplify these bits...
INDEX(ColumnD, 1)="apples"
That is pretty much saying the same as D1="apples"
 
Upvote 0
I THINK the main issue I'm having is that when I'm trying to convert the formula to an array formula, the way I'm using ColumnD as a "left-lookup" for ColumnA is causing the problem. When converted to an array formula, I need to make sure that ColumnA is "aligned" with the same index as the value in ColumnD. Well, that's what I suspect is the problem. Or maybe I did the conversion to array formula wrong.
 
Upvote 0
Always hard to try and work on something this complex without some in-depth knowledge of what you are working on.

A few observations though...
- Helper columns can often be used to simplify beasts like that
- array formulas are generally far less efficient than regular formulas
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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