formula help - smallest value excluding blank spaces

W

wayneeustace

New Member
Joined
May 16, 2015
Messages
22
Hi

I have a row of data that has some blank cells (each column represents a year and for some years there was no recorded value).

I need to find the lowest value for the past 2 years where an entry has been recorded.

Example:
Column A(2015) Column B(2014) Column C(2013) Column D(2012)
Row 1 500 blank 450 300

The formula needs to return the answer of 450 because it is the smallest value from the last 2 years where an entry was recorded.

Is there a fix for this ? Additionally, I would need to copy the formula down to the rest of the rows beneath it that have different combinations of values and blank cells.

Any assistance greatly appreciated :)
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
What is to be returned if there is only one number only in the row - or no numbers at all?


Assuming there will always be at least 2 numbers here are two options.

If you only have 4 columns like your example, then you could use the column E formula. This could become very cumbersome if there is actually a lot of columns.

The column F formula is easily expandable to many columns but does require Excel 2010 or later.

Excel Workbook
ABCDEF
12015201420132012Smallest of Last 2Smallest of Last 2
2500450300450450
3600500200100500500
4620700620620
540202020
6100200300100100
Min Last 2
 
Upvote 0
Control+shift+enter, not just enter:
Rich (BB code): 

=IFERROR(1/(1/MIN(IF(COLUMN(A2:D2)<=SMALL(IF(ISNUMBER(A2:D2),
    COLUMN(A2:D2)),MIN(2,COUNT(A2:D2))),IF(ISNUMBER(A2:D2),A2:D2)))),"")
 
Upvote 0
With year headings as you indicated & the possibility of 1 or 0 numbers in the row, then you could also consider these.

Excel Workbook
ABCDEF
12015201420132012Excel 2010+All versions
2500450300450450
3600500200100500500
4620700620620
540202020
6100200300100100
7
8200200200
Min Last 2
 
Upvote 0
Hi Peter - thanks for both replies.
There won't always be 2 entries, so the earlier suggestions aren't suitable.
The latter solutions - E2 isn't expandable so would need to manually adjust each time.
F2 solution doesn't appear to work - when I paste the formula in and press ctrl+shift+enter it just retains the text of the formula in the cell, rather than entering it.

However, your effort is still greatly appreciated - Aladin's answer appears to be working and is expandable for future years' entries so this has solved my most complex issue.

Thank you, Mr Excel - you're great !!
 
Upvote 0
Wayne E said:
Hi Peter - thanks for both replies.
There won't always be 2 entries, so the earlier suggestions aren't suitable.
The latter solutions - E2 isn't expandable so would need to manually adjust each time.
F2 solution doesn't appear to work - when I paste the formula in and press ctrl+shift+enter it just retains the text of the formula in the cell, rather than entering it.

However, your effort is still greatly appreciated - Aladin's answer appears to be working and is expandable for future years' entries so this has solved my most complex issue.

Thank you, Mr Excel - you're great !!
Click to expand...
No problem, and I'm happy if you choose to use Aladin's formula as I agree it does the job perfectly well.

However ..

1. I disagree with you on E2 not being expandable - see below.

2. I think your problem with testing my F2 solution may be that your cell F2 was formatted as Text, hence the formula remaining as such after entry.
You might care to ensure the cell is formatted as General & try again just to see. Either that or your copy/paste included the {} instead of letting the C+S+E entry do that.
I've included an expanded version of that below as well.

Excel Workbook
ABCDEFGHIJ
120152014201320122011201020092008Excel 2010+All versions
2500400300400400
3
4700700700
540424040
6100200300400500600700800100100
72525202525
Min Last 2
 
Last edited:
Upvote 0
Thanks again. And yes you are correct on both suggestions, my cell was formatted as General and I pasted the array brackets as well as ctrl+shift+enter. All fixed now. Cheers :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

himderfella
  • Solved
Do not calculate formula if there are blank cells
Replies
4
Views
495
MARK858
MARK858
N
  • Question
Ignore blanks when calculating smallest value
Replies
4
Views
386
nightracer
N
A
  • Question
Two dimensional dynamic named range over a pivot table not working as I thought
Replies
4
Views
254
ashleighbrown
A
T
  • Solved
=IF Formula Question
Replies
5
Views
250
Fluff
Fluff
M
  • Solved
How to ignore non numeric values with TAKE function?
Replies
5
Views
351
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,214,523
Messages
6,120,028
Members
448,940
Latest member
mdusw

Share this page

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