Track the last input qty value in a row

[bRicaNz]

Hi everyone,

Need some help. I'm doing our Purchase Report of all items.
I categorized them by month.

for example. I have this data.

<table border="1">
<tr>
<th>Jan</th>
<th>Feb</th>
<th>Mar</th>
<th>April</th>
<th>May</th>
</tr>

<tr>
<td>120</td>
<td>0</td>
<td>45</td>
<td>68</td>
<td>9</td>
</tr>

<tr>
<td>99</td>
<td>81</td>
<td>6</td>
<td>11</td>
<td>10</td>
</tr>

<tr>
<td>611</td>
<td>86</td>
<td>9</td>
<td>11</td>
<td>87</td>
</tr>

<tr>
<td>0</td>
<td>12</td>
<td>6</td>
<td>11</td>
<td>86</td>
</tr>

<tr>
<td>120</td>
<td>9</td>
<td>0</td>
<td>11</td>
<td>0</td>
</tr>

</table>

Now I want to know or get the last Qty value of the month...
The case is I cannot trace the last Qty value of the last purchase.
Because not every month I purchase.

For example. For the month of May I purchase "0" Qty. Then Last April I purchase "160" then I should get the last value from April which is "160".

Does anyone can help with this? Thanks...

 

[Aladin Akyurek]

Yes, it doesn't work....

In my actual sheet I have 14 column...

which start from the month of January 2010 and end month is February 2011.

In some rows it work. But some it get the sum all the values....

The formula, be assured, does not do any summing.

What is your exact range? Do you have formulas in the cells of that range? If yes, care to post one of them here?

 

[bRicaNz]

I don't have any formulas in the range...I don't know what is the exact problem.

row range is C5:Q5...nyahahaha

 

[Aladin Akyurek]

I don't have any formulas in the range...I don't know what is the exact problem.

row range is C5:Q5...nyahahaha

R5:

=LOOKUP(9.99999999999999E+307,1/C5:Q5,C5:Q5)

should return the last non-zero numeric value.

If there is a series for which it does fail, would you post just that record?

 

[bRicaNz]

In range C20:Q20.

I have this data...


96.00 48.00 0.00 0.00 48.00 0.00 0.00 72.00 0.00 0.00 0.00 0.00 0.00 0.00

It should return 72, instead it total the value into 264.00....

 

[Peter_SSs]

In range C20:Q20.

I have this data...


96.00 48.00 0.00 0.00 48.00 0.00 0.00 72.00 0.00 0.00 0.00 0.00 0.00 0.00

It should return 72, instead it total the value into 264.00....

C20:Q20 is 15 columns. The data posted only appears to be 14 items. Is something missing? Perhaps you have a sum formula in the 15th column?

 

[bRicaNz]

I only have 14 colums sir, Because 12months + 2months, which is from Jan2010 to Feb2011.

The range is correct sir....tnx

 

[Peter_SSs]

I only have 14 colums sir, Because 12months + 2months, which is from Jan2010 to Feb2011.

The range is correct sir....tnx

Well, if you only have 14 columns, they are not columns C:Q because that is 15 columns. So you would need to adjust the formula to reflect the columns your data actually occupies.

 

[syntaxed]

I bet column O is hidden. And that column O sums the qtys for the first 12 months (C:N). So that'd explain why the lookup function is returning 264.

 

[bRicaNz]

Yes the lookup function work...

Syntaxed is correct Column O is hidden....
My supervisor just hand over this work to me..

Thanks for the help guys...it's a big help...

 

[Peter_SSs]

I bet column O is hidden. And that column O sums the qtys for the first 12 months (C:N). So that'd explain why the lookup function is returning 264.

Excellent guess.

So, perhaps one of these adaptations could be used.

Excel 2007+
=IFERROR(LOOKUP(9.99999999999999E+307,1/P5:Q5,P5:Q5),LOOKUP(9.99999999999999E+307,1/C5:N5,C5:N5))

Excel 2003-
=IF(AND(MAX(P5:Q5)=0,MIN(P5:Q5)=0),LOOKUP(9.99999999999999E+307,1/C5:N5,C5:N5),LOOKUP(9.99999999999999E+307,1/P5:Q5,P5:Q5))