Average, V or H lookup, match, percentage crazy formula help needed

[sycodiz]

Wondering if someone out there can help me with a crazy formula needed.

What I am trying to do:
In J, Find the audit date in A2 within c1, d1, e1, etc.
Starting with that found amount, has the amounts after steadily increased?
What percentage over the average inventory has the store increased in inventory since audit?
So row 2 I want the Increase column J to know starting at column D (since the audit date matched in C) what was the increase average each month after the audit?
In K, I would like to know what is increase % from the average inventory balance (column i) over the audit date (random column)

Any help would be greatly appreciated!


[TABLE="class: outer_border, width: 766"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Audit date[/TD]
[TD]Mo.[/TD]
[TD]Nov 17 [/TD]
[TD]Dec 17 [/TD]
[TD]Jan 18 [/TD]
[TD]Feb 18 [/TD]
[TD]Mar 18 [/TD]
[TD]Apr 18 [/TD]
[TD]Average Inventory Balance[/TD]
[TD]Increase?[/TD]
[TD]Increase %[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]11/27/2017[/TD]
[TD]Nov 17[/TD]
[TD] 90,828.71 [/TD]
[TD] 96,677.39 [/TD]
[TD] 99,356.74 [/TD]
[TD] 97,436.88 [/TD]
[TD] 106,381.39 [/TD]
[TD] 105,779.63 [/TD]
[TD] 101,126.41 [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12/20/2017[/TD]
[TD]Dec 17[/TD]
[TD] 88,108.74 [/TD]
[TD] 72,260.83 [/TD]
[TD] 76,499.91 [/TD]
[TD] 75,206.23 [/TD]
[TD] 74,354.31 [/TD]
[TD] 70,069.97 [/TD]
[TD] 73,678.25 [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]11/2/2017[/TD]
[TD]Nov 17[/TD]
[TD] 67,612.36 [/TD]
[TD] 61,424.47 [/TD]
[TD] 65,234.93 [/TD]
[TD] 65,621.47 [/TD]
[TD] 71,842.71 [/TD]
[TD] 81,129.74 [/TD]
[TD] 69,050.66 [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]12/5/2017[/TD]
[TD]Dec 17[/TD]
[TD] 134,051.16 [/TD]
[TD] 137,509.69 [/TD]
[TD] 136,774.92 [/TD]
[TD] 136,089.10 [/TD]
[TD] 143,397.25 [/TD]
[TD] 157,493.94 [/TD]
[TD] 142,252.98 [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]12/13/2017[/TD]
[TD]Dec 17[/TD]
[TD] 98,925.82 [/TD]
[TD] 99,772.25 [/TD]
[TD] 101,824.30 [/TD]
[TD] 117,076.87 [/TD]
[TD] 110,627.95 [/TD]
[TD] 117,940.16 [/TD]
[TD] 109,448.31 [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col span="2"><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]

 

[Eric W]

I'm not entirely clear on what you want, but try these formulas:

J2: =EXP(LN(K2+1)/(COLUMNS(C2:H2)-MATCH(A2,C$1:H$1)))-1

K2: =I2/HLOOKUP(A2,C$1:H2,ROW())-1

I'm assuming that C1:H1 are real Excel dates, set to the first of the month and formatted with the month/year.