Forum Discussion
Retrieving the smallest currency number from another sheet, excluding N/A and zeros
Better if you attach sample sheet to illustrate how your data is structured.
- how do we know where are currencies in Sheet2?
- would you like to find smallest in row, in column or in entire sheet?
Sergei,
Having a little trouble with my excel at the moment but here is a screenshot of a section of the data. The array of data I need to pull from starts in C8 and goes to X41, so C8:X41. As you can see in the screen shot under each store there is a value calculation. In the above array I need to find the smallest result and return it to a summary sheet. I hope this helps and thank you for responding.
Benjamin,
It's hard to understand from your screenshot where which columns/rows are, but the idea is
=AGGREGATE(15,6,1/(MOD(COLUMN($C$8:$X$8)-COLUMN($C$8),2)=0)/(MOD(ROW($C$8:$C$41)-ROW($C$8),6)=0)/(C8:X41<>0)*C8:X41,1)
We check every second column and every sixth row, only to adjust from which cell to calculate.
Please see attache sample to illustrate.