Change from IF to something else

[stylemonk]

Hi

In sheet below I managing my stock levels.
Please go to Cell F8, formula there checks In which week (red table) I will run out of my stock and tells me when I have to place new order. Example: Available stock 897400 by taking away usage from each week I will have to place order on 13/07/2015. Everything works, but.

Question : Is there other option to get what I want without Ifs or not that many ifs.
Thanks




Link to file

[samba_ravi]

F8=lookup(sum($d8:$d8),subtotal(9,offset($j8,0,0,1,column($j8:$ba8)-column($j$8)+1)),$k$7:$bb$7)
try this and copy towards down

[stylemonk]

Awesome!!!
Thanks

[samba_ravi]

You are welcome and thanks for your feedback

[stylemonk]

Could you please explain how does it works. Would like to understand and educate my self.

I am so confused I just don't understand what this bit does COLUMN($J8:$BA8)-COLUMN($J$8)+1

UPDATE: I already checked what COLUMN function does. Then I tried to copy COLUMN($J8:$BA8) in to empty cell and it gives me 10 due to J8 is 10th column, when I did the same with COLUMN($J$8)+1 it gave me 11, so 10-11 its -1, so why just not write offset($j8,0,0,1,-1 ???
Please explain somebody

Thanks

[stylemonk]

Anyone ?

[Jonmo1]

UPDATE: I already checked what COLUMN function does. Then I tried to copy COLUMN($J8:$BA8) in to empty cell and it gives me 10 due to J8 is 10th column, when I did the same with COLUMN($J$8)+1 it gave me 11, so 10-11 its -1, so why just not write offset($j8,0,0,1,-1 ???

You have the order of calculuations wrong.
With similar math operators (- is similar to +, * is similar to /)
The operations are done from left to right.

So it's not 10-(10+1) ... 10-(11)
It's actually (10-10)+1 ... (0)+1

And as a whole
COLUMN($J8:$BA8)-COLUMN($J$8)+1
The first column function creates an array of all the column numbers from J to BA {10,11,12,13,14,etc..}
So it becomes
{10,11,12,13,14,etc..}-COLUMN($J$8)+1
{10,11,12,13,14,etc..}-10+1
{0,1,2,3,4,etc..}+1
{1,2,3,4,5,etc..}

Hope that helps.

[stylemonk]

You have the order of calculuations wrong.
With similar math operators (- is similar to +, * is similar to /)
The operations are done from left to right.

So it's not 10-(10+1) ... 10-(11)
It's actually (10-10)+1 ... (0)+1

And as a whole
COLUMN($J8:$BA8)-COLUMN($J$8)+1
The first column function creates an array of all the column numbers from J to BA {10,11,12,13,14,etc..}
So it becomes
{10,11,12,13,14,etc..}-COLUMN($J$8)+1
{10,11,12,13,14,etc..}-10+1
{0,1,2,3,4,etc..}+1
{1,2,3,4,5,etc..}

Hope that helps.

Thanks for explanation.The problem now is how to understand formula in total in simple terms. I do apologies for not understanding and huge thank you guys for your explanations.

OFFSET function, width() part determine the range horizontally, so if its 1 then single cell if 2 then 2 cells etc... In this case, why Column() function is used ? I mean, I could Offset(J8,0,0,1,number of cells used) but it doesn't work this way.

Question: How this

10,11,12,13,14,etc..}-COLUMN($J$8)+1
{10,11,12,13,14,etc..}-10+1
{0,1,2,3,4,etc..}+1
{1,2,3,4,5,etc..}

is used to get to end result?

[Jonmo1]

Let's take it further

=lookup(sum($d8:$d8),subtotal(9,offset($j8,0,0,1,column($j8:$ba8)-column($j$8)+1)),$k$7:$bb$7)
If this part
column($j8:$ba8)-column($j$8)+1
becomes
{1,2,3,4,5,etc..}
Then the whole formula becomes
=lookup(sum($d8:$d8),subtotal(9,offset($j8,0,0,1,{1,2,3,4,5,etc..})),$k$7:$bb$7)

Now this creates 44 ranges by using offset
offset($j8,0,0,1,1) = J8
offset($j8,0,0,1,2) = J8:K8
offset($j8,0,0,1,3) = J8:L8
offset($j8,0,0,1,4) = J8:M8
offset($j8,0,0,1,5) = J8:N8
etc..

=lookup(sum($d8:$d8),subtotal(9,{J8, J8:K8, J8:L8, J8:M8, J8:N8, etc..}),$k$7:$bb$7)

Lets say J8:N8 = 1 2 3 4 5 respectively..
Subtotal does the sum of visible cells, so liets say M8 is NOT visible, the rest are..
Subtotal(9,J8) = 1
Subtotal(9,J8:K8) = 3
Subtotal(9,J8:L8) = 6
Subtotal(9,L8:M8) = 6 (because M8 is not visible and ignored)
Subtotal(9,L8:N8) = 11
=lookup(sum($d8:$d8),subtotal(9,{J8, J8:K8, J8:L8, J8:M8, J8:N8, etc..}),$k$7:$bb$7)
becomes
=lookup(sum($d8:$d8),{1,3,6,6,11, etc..},$k$7:$bb$7)


Hope that helps a bit more..

[Jonmo1]

Come to think of it, the use of subtotal has nothing to do with if cells are hidden or not.
Subtotal only ignores hidden ROWS, not columns..
Looks like it's purpose is to create the array of results from the offset/column functions.