Need help with a fomrmula

M

mduntley

Board Regular
Joined
May 23, 2015
Messages
139
Office Version
  1. 365
Platform
  1. Windows
Hello. I want to get some help on understanding a formula. The reason is that I want to use it for my job. I have created a spreadsheet to help with what I am having a tough time with.

This is the formula that I am using

{=IFERROR(INDEX(B3:B49,SMALL(IF(A3:A49=C$2,ROW(B3:B49)-MIN(ROW(B3:B49))+1),ROWS($C$3:C3))),"")}


I dont know why the odds are only showing.

[TABLE="class: grid, width: 321"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Wattage[/TD]
[TD]What I am getting[/TD]
[TD]What I want[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]12/12/2015[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]13[/TD]
[TD] [/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]14[/TD]
[TD] [/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]15[/TD]
[TD] [/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]16[/TD]
[TD] [/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]17[/TD]
[TD] [/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]18[/TD]
[TD] [/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]19[/TD]
[TD] [/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]20[/TD]
[TD] [/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]21[/TD]
[TD] [/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]22[/TD]
[TD] [/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]23[/TD]
[TD] [/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD="align: right"]12/13/2015[/TD]
[TD="align: right"]24[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12/13/2015[/TD]
[TD="align: right"]25[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12/13/2015[/TD]
[TD="align: right"]26[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12/13/2015[/TD]
[TD="align: right"]27[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12/13/2015[/TD]
[TD="align: right"]28[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12/13/2015[/TD]
[TD="align: right"]29[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12/13/2015[/TD]
[TD="align: right"]30[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12/13/2015[/TD]
[TD="align: right"]31[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12/13/2015[/TD]
[TD="align: right"]32[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12/13/2015[/TD]
[TD="align: right"]33[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12/13/2015[/TD]
[TD="align: right"]34[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12/13/2015[/TD]
[TD="align: right"]35[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12/13/2015[/TD]
[TD="align: right"]36[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12/13/2015[/TD]
[TD="align: right"]37[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12/13/2015[/TD]
[TD="align: right"]38[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12/13/2015[/TD]
[TD="align: right"]39[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12/13/2015[/TD]
[TD="align: right"]40[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12/13/2015[/TD]
[TD="align: right"]41[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12/13/2015[/TD]
[TD="align: right"]42[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12/13/2015[/TD]
[TD="align: right"]43[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12/13/2015[/TD]
[TD="align: right"]44[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12/13/2015[/TD]
[TD="align: right"]45[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12/13/2015[/TD]
[TD="align: right"]46[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12/13/2015[/TD]
[TD="align: right"]47[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
You need to lock your ranges. Right now as you move down a cell your range changes. Also, I don't see the need for the MIN in your formula.

Try changing your formula to (it's still an array formula that must be entered with CTRL-SHIFT-ENTER):
Code: 
[TABLE="width: 711"]
<colgroup><col width="711"></colgroup><tbody>[TR]
   [TD="width: 711"]IFERROR(INDEX($B$3:$B$49,SMALL(IF($A$3:$A$49=C$2,ROW($B$3:$B$49)-ROW($B$3)+1),ROWS($C$3:C3))),"")[/TD]
 [/TR]
</tbody>[/TABLE]

It looks like you could just use this non-array formula:
Code: 
[TABLE="width: 711"]
<colgroup><col width="711"></colgroup><tbody>[TR]
   [TD="width: 711"]=IF($A3=C$2,$B3,"")
[/TD]
 [/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Help Please
2 3
Replies
21
Views
532
Asbestos_Jen
Asbestos_Jen
E
  • Solved
Ranking Invoices By Highest Amount By "Year to Date"
Replies
2
Views
159
ellison
E
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
126
Peter_SSs
Peter_SSs
R
  • Solved
Read through XML file
Replies
1
Views
414
rjtaylor
R
A
  • Solved
Count entry by time
Replies
3
Views
253
admvlad
A

Forum statistics

Threads
1,224,823
Messages
6,181,179
Members
453,021
Latest member
Justyna P

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