Forum Discussion
search between columns
I has this truncate table show below:
Device is from 1 to few hundreds.
Vref is from 1.0 to 5.0
Current starts from 0 then having some values and then goes down to 0.
I need to return a Vref value whenever the current goes from zero to a value (i.e Vref = 1.4, and 2.8).
I'm a basic excel user. Could you help?
Depends on which Excel you are. For 365 it could be
with
=IFNA( IF({1,0}, FILTER($B$4:$B$33,($C$3:$C$32=0)*($C$4:$C$33<>0)), FILTER($B$4:$B$33,($C$4:$C$33<>0)*($C$5:$C$34=0)*($C$5:$C$34<>"")) ),"")
8 Replies
That's first part of formula from previous sample
=IFNA( FILTER($B$4:$B$33,($C$3:$C$32=0)*($C$4:$C$33<>0)),"")Thanks Sergei. The long formula seems to partially work in my case.
1/ How can I make it to work for say 20 devices?
2/ How can I expanse for it to work if I have more Vref values per device? Currently there are 11 values. What if there are 20? I try to change the row index accordingly but it fails. Please see file attach.
Depends on which Excel you are. For 365 it could be
with
=IFNA( IF({1,0}, FILTER($B$4:$B$33,($C$3:$C$32=0)*($C$4:$C$33<>0)), FILTER($B$4:$B$33,($C$4:$C$33<>0)*($C$5:$C$34=0)*($C$5:$C$34<>"")) ),"")- You haven't described the Criteria ,,, like considering the results 1.4 & 2.8 ,,, if as you have written if current goes ZERO to the Value the WHAT VALUE,, I mean what are LOWEST & HIGHEST current,,, and is only one Criteria range for example you want to get Vref for current 0 to 1 only or is like 0 to 1 the 1 to 3,, better share complete information !!
