Scratching my head

[jarhead58]

Scenario: [TABLE="width: 238"]
<tbody>[TR]
[TD="align: right"]B2:B5
39[/TD]
[TD="align: right"]
48[/TD]
[TD="align: right"]
53[/TD]
[TD="align: right"]
67[/TD]
[TD="align: right"]
68
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

L53:P53
12 14 20 39 40

Why wouldn't this formula give back a "TRUE" value? I did do the Ctrl-Shift-Enter for the formula.

=INDEX(B2:F5,MATCH(B2:B5,L53:P53,0),"TRUE","FALSE")

TIA

 

[Scott Huish]

=or(sumproduct(countif(b2:f2,l5353))>2,and(sumproduct(countif(b2:f2,l5353))>1,g2=g53))

 

[jarhead58]

=or(sumproduct(countif(b2:f2,l5353))>2,and(sumproduct(countif(b2:f2,l5353))>1,g2=g53))

Thanks for the reply! I will give this a shot.

On another note, I thought this would be fairly simple but evidently not! I have a cell that either will get an "NA" result or a dollar amount based on the existing formula. What I want to do is just shift the "NA" value to the right in all of the cells that contain "NA".

Getting a circular reference error with this:

=IF(G8="","NA",G8*0.65)*(RIGHT(L8:L9827,2))
TIA

 

[Scott Huish]

I'm thinking that you don't quite understand what the RIGHT function does. It returns x # of characters from a cell, it doesn't position text in a cell at all.

For example if A1 contained ABCDEF
and you used this formula =RIGHT(A1,2) it would return EF

Just click on the right justification button on the Home tab to position all values in your formula to the right.

 

[jarhead58]

I'm thinking that you don't quite understand what the RIGHT function does. It returns x # of characters from a cell, it doesn't position text in a cell at all.

For example if A1 contained ABCDEF
and you used this formula =RIGHT(A1,2) it would return EF

Just click on the right justification button on the Home tab to position all values in your formula to the right.

Ah, looking at it too literally lol! I only want the cells with the "NA" to position right though, not the ones with the dollar amounts! They are in the same column by the btw. Is it possible?

 

[Scott Huish]

Custom format (Number Format) the cell as:
General;-General;0;* @

 

[jarhead58]

Custom format (Number Format) the cell as:
General;-General;0;* @

That works good enough! Just for my own info, is there a way to put the $ with the amount? It is shifted away from the numbers?

TIA

 

[Scott Huish]

Something like this. It's just the accounting format with the change to the text format at the end.

_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);* @

 

[jarhead58]

Something like this. It's just the accounting format with the change to the text format at the end.

_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);* @

Thank You very much Scott!!