Add to formula

[Pat]

I want to add to the following formula a condition if PO04!$T$24:$T$1500
contains the letter "E" a value will be returned, if not leave cell empty.

{=IF(ISERROR(INDEX(PO04!$L$24:$L$1500,MATCH(PC!$H8,PO04!$AI$24:$AI$1500,0)))
,"",INDEX(PO04!$L$24:$L$1500,MATCH(PC!$H8,PO04!$AI$24:$AI$1500,0)))}

Thanking you if you can be of help.
Pat

 

[Dave R.]

To clarify -- if ALL cells t24:t1500 contain "E"?
Does "E" have to be alone, or any occurence of "E" anywhere in the range
(i.e., "apple" would count also), again for all cells or any one cell?

 

[Pat]

More than one cell will contain the letter "E"
"E" will have to be alone

 

[Dave R.]

=IF(COUNTIF(PO04!T24:T1500,"E"),enter your formula here ,"")

 

[Pat]

Here is the formula when adjusted. It does not return a value as expected,
although there is no errors appearing.

=IF(COUNTIF(PO04!T24:T1500,"E"),IF(ISERROR(INDEX(PO04!$L$24:$L$1500,MATCH(PC
!$H8,PO04!$AI$24:$AI$1500,0))),"",INDEX(PO04!$L$24:$L$1500,MATCH(PC!$H8,PO04
!$AI$24:$AI$1500,0))),"")

Because PO04!T24:T1500 is formmated to either TRUE or FALSE ("E" being TRUE
and "S" being FALSE) when the formula is adjusted to reflect this for eg.

=IF(COUNTIF(PO04!T24:T1500,1),IF(ISERROR(INDEX(PO04!$L$24:$L$1500,MATCH(PC!$
H8,PO04!$AI$24:$AI$1500,0))),"",INDEX(PO04!$L$24:$L$1500,MATCH(PC!$H8,PO04!$
AI$24:$AI$1500,0))),"")

a value is returned. The only problem now is that a value will return for a
given cell both if 1(one) or 0 (zero) is used in the same formula.

I hope you can understand where I am coming from!
Pat

 

[Dave R.]

You just need to figure out what is actually contained in those cells
(t24:t1500). go to some generally formatted cell and enter =T24 , and see
what comes up. Also see what comes up on cells that are the opposite
(true/false), and use that in the COUNTIF part.

Of course if there is at least one 0 and one 1 in that range, the formula
will return the same thing.

 

[Pat]

Of course if there is at least one 0 and one 1 in that range, the formula

will return the same thing.

Each cell in (t24:t1500) contains either "E" or "S" I have changed the
format so it nolonger is a TRUE or FALSE value. Even with this change the
result is the same. Will a different approach be required?

 

[Dave R.]

Again you must be sure what the content of the cells are. If they are simply
E or S, your formula would work fine. If there is one or more "E" in that
range, it will do the formula, if not it will show a blank.

 

[Pat]

For the life of me I am sorry but I cannot see how the formula will do what
I want. If the corresponding cell is an "S" and should be an "E" it should
return a blank cell.

If you care to send me a sample workbook of how it works this would be most
welcome. Please email (e-mail address removed)

 

[Myrna Larson]

I don't understand... You say "... cell is an 'S' and should be an 'E'". A
formula can't "know" that you've made a data entry error unless you build the
logic to do that into the formula.

 

[Dave R.]

To be honest I am not quite sure how you want it to work. I thought you said
you wanted to show the formula result if there were any "E" in that
T24:T1500 range.

I think you understand this already, but =COUNTIF(T24:T1500,"E") will count
the number of cells that are equal to "E" in T24:T1500. When this COUNTIF is
embedded inside =IF as in =IF(COUNTIF , the IF statement is TRUE if the
COUNTIF formula returns a number 1 or above, and FALSE if the COUNTIF
returns 0.

In other words, if you have one or 1400 "E" in that range, the formula would
be TRUE, and your longer formula would be computed and displayed. If there
are no "E" in that range, the longer formula would not be displayed or
calculated.

Re-reading your original post, maybe you want to show the formula result
only if ALL CELLS in T24:T1500 are "E"s?

You can try an approach like this if that's what you wanted (feel free to
swap "S" for "E"):

=IF(COUNTIF(T24:T1500,"E")=ROWS(24:1500),enter your formula here,"")

 

[Pat]

To be honest I am not quite sure how you want it to work. I thought you
said

you wanted to show the formula result if there were any "E" in that
T24:T1500 range.

Yes this is true but I think I need to give you a clear example of what I am
looking for.

=IF(COUNTIF(PO04!T24:T1500,"E")=ROWS(24:1500),IF(ISERROR(INDEX(PO04!$L$24:$L
$1500,MATCH(PC!$H8,PO04!$AI$24:$AI$1500,0))),"",INDEX(PO04!$L$24:$L$1500,MAT
CH(PC!$H8,PO04!$AI$24:$AI$1500,0))),"")

PO04!$L$24:$L$1500 = price
PC!$H8 = Fruit name
PO04!$AI$24:$AI$1500 = Fruit name (corresponds to PC!$H8 = Fruit name
PO04!T24:T1500 = currency "E" for euro and "S" for sterling

Ok, the formula will be contained in PC!J8 If an apple is entered into cell
PC!H8 the formula will look in PO04!$AI$24:$AI$1500 to find a matching cell.
Lets say the matching cell is PO04!AI256. If PO04!T256 then contains the
letter "E" the price from PO04!L256 will be returned. If PO04!T256
contains the letter "S" or is empty the price will not be displayed.

Hope this clarifies what I am looking for.

 

[Dave R.]

Ah so you needed help with the existing formula!

You can look here;
http://groups.google.com/groups?selm=#[email protected]&output=gplain


And use the index/match setup there. It uses MATCH(1 because following that
it multiplies the criteria together and when both criteria are met, a one
will be calculated and you'll get the corresponding value from the INDEX
part.

It will work well if you don't have any duplicates. If you don't want to see
#NA, simply wrap an ISNA around it like;

=IF(ISNA(formula),"",formula)

 

[Pat]

It will work well if you don't have any duplicates.

Unfortunately there will be duplicates, besides it will not be practicable
to include 2 values like ="L" and ="TECH" into the formula. Only one is
possible. Also I am linking 2 sheets together so the formula will need to
take account of this.

=INDEX($A$1:$A$3,MATCH(1,($B$1:$B$3="L")*($C$1:$C$3="TECH"),0))

I would be surprised if there is not a formula to do what I want it to do.

 

[Dave R.]

Pat, this is not very well thought out, is it? If you have duplicates (e.g.,
more than 1 instance of "apple"), you haven't said anything about what to do
when a duplicate is encountered.

You would not include "L" and "tech" in the formula, you would put
references to the cells containing "apple" and "s".

I am certain there are formulas to do what you want, but I for one am again
unsure of what you're trying to do. Maybe someone else can give you a hand.

Good luck.

 

[Pat]

Thank you Dave for trying to help me out, it can at time be difficult to
express what the problem is in order for some to help.

I know you have gone a far as you can with this, but if you would like me to
send you a sample workbook this will show clearly where I am coming from.
Let me know if you can accept a sample.

Pat

 

[Dave R.]

Pat, I think the best thing you could do is do some google group searches in
the excel groups. I'm sure a question such as yours has already been covered
some time in the past, youll just have to apply what they've done to your
situation.