formula required to get value of a range

H

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
1,226
Office Version
  1. 2010
Platform
  1. Windows
  2. Mobile
In A2 I need a formula to get value of range C2:L2 for which C3:L3 is ZERO "0"
At a time there will be only 1 0 in C3:L3

Thanks in advance for the help.

Sheet1

ABCDEFGHIJKL

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]7[/TD]

[TD="align: right"]200[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

</tbody>
 
Sort by date Sort by votes

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.
Please pardon me for my additions…
C3:L3 is either 0 or 1
By default C3:L3 is 1
0 appears starting C3 & goes up to L3 (Right to Left)
So at a given time there can be 1 or up to 10 0s in C3:L3

I want formula to consider “Last but one” appearance of 0 in C3:L3


ABCDEFGHIJKL

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 33px"><col style="WIDTH: 33px"><col style="WIDTH: 33px"><col style="WIDTH: 33px"><col style="WIDTH: 33px"><col style="WIDTH: 33px"><col style="WIDTH: 33px"><col style="WIDTH: 33px"><col style="WIDTH: 33px"><col style="WIDTH: 33px"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]5[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>
 
Upvote 0
hsandeep said:
Please pardon me for my additions…
C3:L3 is either 0 or 1
By default C3:L3 is 1
0 appears starting C3 & goes up to L3 (Right to Left)
So at a given time there can be 1 or up to 10 0s in C3:L3

I want formula to consider “Last but one” appearance of 0 in C3:L3


ABCDEFGHIJKL

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 33px"><col style="WIDTH: 33px"><col style="WIDTH: 33px"><col style="WIDTH: 33px"><col style="WIDTH: 33px"><col style="WIDTH: 33px"><col style="WIDTH: 33px"><col style="WIDTH: 33px"><col style="WIDTH: 33px"><col style="WIDTH: 33px"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]5[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>
Click to expand...

Try:

=LOOKUP(9.99999999999999E+307,1/ISERROR(1/B3:INDEX(B3:K3,MATCH(9.99999999999999E+307,C3:L3)-1)),C2:INDEX(C2:L2,MATCH(9.99999999999999E+307,C3:L3)-1))

Note that this formula will treat in-between empty cells as zeros.
 
Upvote 0

Excel 2010
ABCDEFGHIJKL
151020548721110050
20000
Sheet1
Cell Formulas
RangeFormula
A1{=INDEX($C$1:$L$1,LARGE(IF($C$2:$L$2<>"",IF($C$2:$L$2=0,COLUMN($C$2:$L$2)-2)),2))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Aladin Akyurek said:
Try:

=LOOKUP(9.99999999999999E+307,1/ISERROR(1/B3:INDEX(B3:K3,MATCH(9.99999999999999E+307,C3:L3)-1)),C2:INDEX(C2:L2,MATCH(9.99999999999999E+307,C3:L3)-1))

Note that this formula will treat in-between empty cells as zeros.
Click to expand...

Edit for range specification:

=LOOKUP(9.99999999999999E+307,1/ISERROR(1/C3:INDEX(C3:L3,MATCH(9.99999999999999E+307,C3:L3)-1)),C2:INDEX(C2:L2,MATCH(9.99999999999999E+307,C3:L3)-1))
 
Upvote 0
No it is not as desired:
It is considering Last 0 occurrence in C3:L3.
I want the formula should consider “Last minus one” occurrence.
Plus it should not consider C3 even if C3 is 0.

Ex:
If C3=0, C4=0, C5=0

Currently it is considering C5 & giving output as B5 (WRONG)
I require: it should consider C4 (Last minus one) & give output as B4 (CORRECT)
 
Upvote 0
hsandeep said:
No it is not as desired:
It is considering Last 0 occurrence in C3:L3.
I want the formula should consider “Last minus one” occurrence.
Plus it should not consider C3 even if C3 is 0.

Ex:
If C3=0, C4=0, C5=0

Currently it is considering C5 & giving output as B5 (WRONG)
I require: it should consider C4 (Last minus one) & give output as B4 (CORRECT)
Click to expand...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][td]
H​
[/td][td]
I​
[/td][td]
J​
[/td][td]
K​
[/td][td]
L​
[/td][/tr][tr][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
2​
[/td][td]
5​
[/td][td][/td][td]
10​
[/td][td]
20​
[/td][td]
5​
[/td][td]
4​
[/td][td]
8​
[/td][td]
7​
[/td][td]
2​
[/td][td]
11​
[/td][td]
100​
[/td][td]
50​
[/td][/tr]
[tr][td]
3​
[/td][td][/td][td][/td][td]
0​
[/td][td]
0​
[/td][td]
0​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
4​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
5​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
6​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
7​
[/td][td]
5​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

A7 houses:
Rich (BB code): 
=LOOKUP(9.99999999999999E+307,1/ISERROR(1/C3:INDEX(C3:L3,MATCH(9.99999999999999E+307,C3:L3)-1)),
     C2:INDEX(C2:L2,MATCH(9.99999999999999E+307,C3:L3)-1))
 
Upvote 0
Yes it WORKS.
I want to add: If answer=#N/A or Z100=1 then ANSWER should be "" (NULL) else as per your formula.
 
Upvote 0
hsandeep said:
Yes it WORKS.
I want to add: If answer=#N/A or Z100=1 then ANSWER should be "" (NULL) else as per your formula.
Click to expand...

Try...

Code: 
=IF(Z100=1,"",IFERROR(LOOKUP(9.99999999999999E+307,1/ISERROR(1/C3:INDEX(C3:L3,MATCH(9.99999999999999E+307,C3:L3)-1)),
     C2:INDEX(C2:L2,MATCH(9.99999999999999E+307,C3:L3)-1)),"")

If you define BigNum in the Name Manager as referring to:

=9.99999999999999E+307

we can rewrite our formula as:

Code: 
=IF(Z100=1,"",IFERROR(LOOKUP(BigNum,1/ISERROR(1/C3:INDEX(C3:L3,MATCH(BigNum,C3:L3)-1)),
     C2:INDEX(C2:L2,MATCH(BigNum,C3:L3)-1)),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA code clean up
Replies
9
Views
445
mumps
mumps
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
512
smozgur
smozgur
E
  • Question Question
Adding a custom message.
Replies
1
Views
621
Micron
M
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,222,690
Messages
6,167,666
Members
452,131
Latest member
MichelleH77

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