Array Formula : IF not giving TRUE/FALSE results

V

VinayShankam

New Member
Joined
Jul 25, 2013
Messages
23
In the attached file in the 'Overdue List' tab I am trying to list all the projects that are overdue. File URL https://www.dropbox.com/s/28atfuxvwe5xstl/Test.xlsx

I get the count of over due projects from =COUNTIFS('Master List'!$G$2:$G$60,">=6",'Master List'!$G$2:$G$60,"<150") - This is working fine

But when I try to generate a list using this array formula

=IF(ROWS(A$2:A2)<=$J$1,INDEX('Master List'!$D$2:$D$60,SMALL(IF('Master List'!$G$2:$G$60,">=6", IF('Master List'!$G$2:$G$60,"<150", ROW('Master List'!$F$2:$F$60)-ROW('Master List'!$F$2)+1)),ROWS(A$2:A2))),"")

IF conditions IF('Master List'!$G$2:$G$60,">=6" and IF('Master List'!$G$2:$G$60,"<150" dont seem to generate TRUE or FALSE result. Hence I am getting a #NUM! error

Please advise what am I doing wrong
 
Sort by date Sort by votes
VinayShankam said:
In the attached file in the 'Overdue List' tab I am trying to list all the projects that are overdue. File URL https://www.dropbox.com/s/28atfuxvwe5xstl/Test.xlsx

I get the count of over due projects from =COUNTIFS('Master List'!$G$2:$G$60,">=6",'Master List'!$G$2:$G$60,"<150") - This is working fine

But when I try to generate a list using this array formula

=IF(ROWS(A$2:A2)<=$J$1,INDEX('Master List'!$D$2:$D$60,SMALL(IF('Master List'!$G$2:$G$60,">=6", IF('Master List'!$G$2:$G$60,"<150", ROW('Master List'!$F$2:$F$60)-ROW('Master List'!$F$2)+1)),ROWS(A$2:A2))),"")

IF conditions IF('Master List'!$G$2:$G$60,">=6" and IF('Master List'!$G$2:$G$60,"<150" dont seem to generate TRUE or FALSE result. Hence I am getting a #NUM! error

Please advise what am I doing wrong
Click to expand...

Control+shift+enter, not just enter:
Rich (BB code): 
=IF(ROWS(A$2:A2)<=$J$1,INDEX('Master List'!$D$2:$D$60,
  SMALL(IF('Master List'!$G$2:$G$60 >= 6, IF('Master List'!$G$2:$G$60 < 150, 
  ROW('Master List'!$F$2:$F$60)-ROW('Master List'!$F$2)+1)),ROWS(A$2:A2))),"")

If so desired, you can replace the IF(ROWS(A$2:A2)<=$J$1 bit with IFERROR.
 
Upvote 0
VinayShankam said:
Yes I have tried Control+shift+enter, not just enter. Still get #NUM! error
Click to expand...

Master List houses the relevant data...

[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]KAD
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]6
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]LAD
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]200
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]FAD
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]120
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]NAD
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]15
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]XAD
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]400
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]ZAD
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Sheet1 houses the processing...

[TABLE="width: 48"]
<tbody>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]Result
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]KAD
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]FAD
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NAD
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

A2, control+shift+enter, not just enter:
Rich (BB code): 
=IFERROR(INDEX('Master List'!$D$2:$D$60,
  SMALL(IF('Master List'!$G$2:$G$60 >= 6, IF('Master List'!$G$2:$G$60 < 150,
  ROW('Master List'!$D$2:$D$60)-ROW('Master List'!$D$2)+1)),ROWS(A$2:A2))),"")

This is essentially the same formula. I only removed the reference to the range in column F. Also replaced the the initial ROWS bit wit IFERROR. But all this has nothing to do with the NUM error you seem to have.
 
Upvote 0
syedbokhari said:
Question Aladin

Why do you CTRL+Shift+Enter ?

What does that initiate and should one do it always?

And when should one CTRL+Shift+Enter?
Click to expand...

Whenever you need multiple results like in

A2:A10="kad"

which leads to a set of evaluations (not a single value) and
the function you use is not of array-processing type such as LOOKUP, FREQUENCY, or SUMPRODUCT.
 
Upvote 0
Please could you recheck your formula,

=IFERROR(INDEX('Master List'!$D$2:$D$60, SMALL(IF('Master List'!$G$2:$G$60 >= 6, IF('Master List'!$G$2:$G$60 < 150, ROW('Master List'!$D$2:$D$60)-ROW('Master List'!$D$2)+1)),ROWS(A$2:A2))),""</pre>it is throwing error.

Also do we not need ROWS(A$2:A2)<=COUNTIFS('Master List'!$G$2:$G$60,">=6",'Master List'!$G$2:$G$60,"<150")
 
Upvote 0
VinayShankam said:
Please could you recheck your formula,

=IFERROR(INDEX('Master List'!$D$2:$D$60, SMALL(IF('Master List'!$G$2:$G$60 >= 6, IF('Master List'!$G$2:$G$60 < 150, ROW('Master List'!$D$2:$D$60)-ROW('Master List'!$D$2)+1)),ROWS(A$2:A2))),""it is throwing error.

Also do we not need ROWS(A$2:A2)<=COUNTIFS('Master List'!$G$2:$G$60,">=6",'Master List'!$G$2:$G$60,"<150")
Click to expand...

It is either:

=IFERROR(INDEX('Master List'!$D$2:$D$60,SMALL(IF('Master List'!$G$2:$G$60 >= 6, IF('Master List'!$G$2:$G$60 < 150,ROW('Master List'!$D$2:$D$60)-ROW('Master List'!$D$2)+1)),ROWS(A$2:A2))),"")

or, with J1 housing:

=COUNTIFS('Master List'!G2:G60,">=6",'Master List'!G2:G60,"<150")

=IF(ROWS($A$2:A2)<=$J$1,INDEX('Master List'!$D$2:$D$60, SMALL(IF('Master List'!$G$2:$G$60 >= 6, IF('Master List'!$G$2:$G$60 < 150,ROW('Master List'!$D$2:$D$60)-ROW('Master List'!$D$2)+1)),ROWS(A$2:A2))),"")

Whichever is chosen, the formula goes in A2 on a destination sheet and pulls of data from sheet Master List.
 
Upvote 0
You are a super star!

I like your better
=IFERROR(INDEX('Master List'!$D$2:$D$60,SMALL(IF('Master List'!$G$2:$G$60 >= 6, IF('Master List'!$G$2:$G$60 < 150,ROW('Master List'!$D$2:$D$60)-ROW('Master List'!$D$2)+1)),ROWS(A$2:A2))),"")

Many Thanks
 
Upvote 0
VinayShankam said:
You are a super star!

I like your better
=IFERROR(INDEX('Master List'!$D$2:$D$60,SMALL(IF('Master List'!$G$2:$G$60 >= 6, IF('Master List'!$G$2:$G$60 < 150,ROW('Master List'!$D$2:$D$60)-ROW('Master List'!$D$2)+1)),ROWS(A$2:A2))),"")

Many Thanks
Click to expand...

You are welcome.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
VBA - Copying Non-Contiguous Ranges in/out of (an) Array(s)
2
Replies
18
Views
530
kvsrinivasamurthy
K
H
  • Solved
Reduce size of (or speed up) excel spreadsheet
Replies
5
Views
340
helpcharity
H
I
  • Solved
Listbox on userform to show results in reverse order
2
Replies
16
Views
671
Fuji
F
T
  • Question Question
Pull data from multiple sheets that match varying criteria
Replies
1
Views
789
bobaol
B
I
  • Solved
Date formula not applied the same to all Listbox entries
Replies
2
Views
104
ipbr21054
I

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