same formula in 2 columns - one works but the other doesn't!!

[FreakyGirl]

hello

I have 2 sets of a 5 column array. BB to BF & CB to CF

they are identical in formulas but the values returned are slightly different. this is expected & acceptable.

column BF is where I'm having an issue. its giving me a #VALUE error even thou the SAME formula in CF works great.

=IF(CE2>=2,CC2+ROW(),CC2)

works in CF

=IF(BE2>=2,BC2+ROW(),BC2)

does not work in BF

=IF(BE2=2,BC2+ROW(),BC2)

works in BF but doesn't help me.


not sure why the ">" would make a diff in the BF formula. I even changed it to >1 thinking that would give me the same result as >=2 but I got the same #VALUE error.

logic:
BB = take the name in AI & give it a unique value.
BC = convert BB from text to a number
BD = rank the number in BB
BE = count many times the value in BC shows up
BF = if the count is 2 or higher then add the row number to BC to break any ties

its the same logic from CB to CF. the only diff is that CB references BS.

what am I missing?

thanks in advance for any help & have a great day!

FreakyGirl

 

[FreakyGirl]

my mistake. in working on another section I noticed the codes were not the same so the problem is in CF as well as BF & its caused by the ">"

logic needed:
If BE2>=2 Is true then add the row number to BC2 breaking the tie. if false, just post BC2.

thanks,
FreakyGirl

 

[Hui]

Change BC2 from: =IF(ISERROR(BB2),"",VALUE(BB2))
to: =IF(ISERROR(BB2),0,VALUE(BB2))
Copy down and recalculate

I'd also do the same in CC2

Another issue could be that CB2 is text not a number
=BW2&SUM(CODE(LEFT(BS2)),CODE(MID(BS2,2,1)),CODE(RIGHT(BS2)),,LEN(BS2),CODE(MID(BS2,LEN(BS2)/2,1)),100)

I would change that to:
=iferror(Value(BW2&SUM(CODE(LEFT(BS2)),CODE(MID(BS2,2,1)),CODE(RIGHT(BS2)),,LEN(BS2),CODE(MID(BS2,LEN(BS2)/2,1)),100)),0)

copy down as well


Lots of your formulas like BW2 have =IF(BV2="",BT2,SUM(BT2,-BV2))
You should try and keep everything as numbers

so BV2 should be
=IF(BU2>=3,INDEX($BZ$2:$BZ$200,MATCH(BS2,$BX$2:$BX$200,0)),0)

Then BW2 becomes
=IF(BV2=0,BT2,SUM(BT2,-BV2))

 

[FreakyGirl]

Change BC2 from: =IF(ISERROR(BB2),"",VALUE(BB2))
to: =IF(ISERROR(BB2),0,VALUE(BB2))
Copy down and recalculate
I'd also do the same in CC2

Hello Hui

TY for the fix on changing the "" to 0. have done to both columns. that fixed the problem.

now I see, the value error in BF/CF was giving me the value error in BD/CD.

yes, CB2 was showing as text so I had CC2 with VALUE. I have since changed it using IF/ISERROR because IFERROR doesn't work for my version & freed up a column!

In the case of BV & BU I wanted any number that did show up there to stand out. Easier to spot when the rest of the column is blank.

Thanks to your lesson of keeping everything to numbers you've proved its better to use a conditional to make them standout instead.

thanks SO much for your help!
FreakyGirl