DOES ANYONE KNOW IF EXCEL READS ITEMS IN A LIST DIFFERENTLY ?

J

johnnyz197034

I NEED TO REFERENCE ITEMS IN A LIST FOR AN IF FORMULA HOWEVER EXCEL DOES NOT
APPEAR TO READ LISTS THE SAME AS IT WOULD IF JUST TEXT WERE IN A CELL.
ANY HELP WOULD BE GREATLY APPRECIATED!!!
 
R

Ron Coderre

1) Can you give an example of what you're tying to do? Include any formulas
you're using and the contents of any cells they might be referencing.

2) Please don't type in all CAPITALS....(It says you're shouting at us.)

***********
Regards,
Ron

XL2002, WinXP
 
J

johnnyz197034

Sorry about the caps.
The formula is =IF(C17="TRANSFER",C18*0.005,0)
I have a list consisting of TRANSFER & REFINANCE in cell C17 and cell C18 is
a loan amount.
If I enter the word TRANSFER in any other cell on the page, the formula
yields the correct result.
It brings back "0" if I use cell C17, so I know the formula is correct.
Thank you for any help u can give me Ron.
 
R

Ron Coderre

Try something like this:

To test if C17 begins with "TRANSFER"...
=COUNTIF(C17,"TRANSFER*")*C18*0.005

OR...to test if C17 contains "TRANSFER"...
=COUNTIF(C17,"*TRANSFER*")*C18*0.005

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
R

Ron Coderre

You're very welcome.....and thanks for the feedback!


***********
Regards,
Ron

XL2002, WinXP
 
J

johnnyz197034

I need help on one more if you would be so kind RonOnce again, I have a list
in a cell.
The list is P.R. and N.O.O.
If it is P.R. I need it to equal 70
If it is N.O.O I need it to equal 125
I may still have the trouble that I had on the last issue that u helped me
with also.
The reference cell is C19.
Any help would be great!!!
Thanx:)
 
R

Ron Coderre

Maybe this?:

=SUM(COUNTIF(C19,{"P.R.","N.O.O."})*{70,125})

Does that help?

Note: if your list grows to more than a few items, you'll need to switch to
another technique.

***********
Regards,
Ron

XL2002, WinXP
 
J

johnnyz197034

Hey Ron,
Works for the N.O.O, but not the P.R.
I manipulated it a little bit but couldn't solve it.
I added asterisks in case that was the problem again but no luck.
Any further ideas?
Thanx!
 
R

Ron Coderre

Here's what I tested.....

B19: =SUM(COUNTIF(C19,{"P.R.","N.O.O."})*{70,125})

When C19 contains only "P.R." the formula returns 70
When C19 contains only "N.O.O." the formula returns 125
When C19 contains anything else,the formula returns 0

If C19 may contain values prepended or appended to "P.R." or "N.O.O."
then this formula may work:
B19: =SUM(COUNTIF(C19,{"*P.R.*","*N.O.O.*"})*{70,125})

Exanple:
If C19 contains "this is my P.R. value" that formula returns 70.

Does that help?

If "no".....please post your formula so we can see what you're dealing with.

***********
Regards,
Ron

XL2002, WinXP
 
J

johnnyz197034

That worked perfect!!!Thank u very much.


Ron Coderre said:
Here's what I tested.....

B19: =SUM(COUNTIF(C19,{"P.R.","N.O.O."})*{70,125})

When C19 contains only "P.R." the formula returns 70
When C19 contains only "N.O.O." the formula returns 125
When C19 contains anything else,the formula returns 0

If C19 may contain values prepended or appended to "P.R." or "N.O.O."
then this formula may work:
B19: =SUM(COUNTIF(C19,{"*P.R.*","*N.O.O.*"})*{70,125})

Exanple:
If C19 contains "this is my P.R. value" that formula returns 70.

Does that help?

If "no".....please post your formula so we can see what you're dealing with.

***********
Regards,
Ron

XL2002, WinXP
Click to expand...
 
J

johnnyz197034

I have a MUCH more complicated task to complete on this workbook now.
It would be impossible to explain it to you without seeing it.
Is there a way I could get it to you then let you know what I need?
 
Top