Trouble with IF

E

EG

I am trying to get a cell to render a positive value or a negative value of a
certain number if another cell has one of two letters. i.e.:

if the value of cell J12 = .28 and the first letter of cell C12 is B, then
the value of cell K12 = (0.28).

if the value of cell J12 = .28 and the first letter of cell C12 is S, then
the value of cell K12 = 0.28.

I am stuck. Can someone shed some light?

eric
 
E

Ed Ferrero

=IF(C12="B",-J12,IF(C12="S",J12,NA()))

What happens if C12 is neither B or S?

Ed Ferrero
 
B

Biff

You didn't define what to do if C12 is neither B nor S or will it *always*
be one or the other?

=IF(AND(LEFT(C12)="B",J12=0.28),-0.28,IF(AND(LEFT(C12)="S",J12=0.28),0.28,""))

Format K12 to the negative style that you want. (0.28)

Biff
 
E

EG

Thanks. Good point. if C12 is neither B or S, I would want the cell value
to render 0. How would I do that?

Eric
 
E

Epinn

Is it true that J12 can be any number other than .28? Can J12 be 0? Can J12 be a negative number?

Assuming J12 is always > 0, in K12, key in this formula:-

=IF(LEFT(C12)="B",-J12,IF(LEFT(C12)="S",J12,0))

Format K12 accordingly.

Epinn

I am trying to get a cell to render a positive value or a negative value of a
certain number if another cell has one of two letters. i.e.:

if the value of cell J12 = .28 and the first letter of cell C12 is B, then
the value of cell K12 = (0.28).

if the value of cell J12 = .28 and the first letter of cell C12 is S, then
the value of cell K12 = 0.28.

I am stuck. Can someone shed some light?

eric
 
B

Biff

Is it true that J12 can be any number other than .28?
I read that to mean "a certain number" is specifically 0.28 which is why I
use AND.

Biff

Is it true that J12 can be any number other than .28? Can J12 be 0? Can
J12 be a negative number?

Assuming J12 is always > 0, in K12, key in this formula:-

=IF(LEFT(C12)="B",-J12,IF(LEFT(C12)="S",J12,0))

Format K12 accordingly.

Epinn

I am trying to get a cell to render a positive value or a negative value of
a
certain number if another cell has one of two letters. i.e.:

if the value of cell J12 = .28 and the first letter of cell C12 is B, then
the value of cell K12 = (0.28).

if the value of cell J12 = .28 and the first letter of cell C12 is S, then
the value of cell K12 = 0.28.

I am stuck. Can someone shed some light?

eric
 
E

Epinn

Yes, I understand why you did it that way.

But I am thinking it may not be .28 for J13, J14, J15 ........ and Eric may still want the numbers in column J show up in column K depending on "S" or "B".

Now, he has two versions to choose from depending on his needs.

Biff, I am not sure if it is better to use -J12 or J12*-1 in my formula. Do you see any difference? Feel free to fix up my formula if you see the need.

Thanks.

Epinn

Biff said:
Is it true that J12 can be any number other than .28?
Click to expand...

I read that to mean "a certain number" is specifically 0.28 which is why I
use AND.

Biff

Is it true that J12 can be any number other than .28? Can J12 be 0? Can
J12 be a negative number?

Assuming J12 is always > 0, in K12, key in this formula:-

=IF(LEFT(C12)="B",-J12,IF(LEFT(C12)="S",J12,0))

Format K12 accordingly.

Epinn

I am trying to get a cell to render a positive value or a negative value of
a
certain number if another cell has one of two letters. i.e.:

if the value of cell J12 = .28 and the first letter of cell C12 is B, then
the value of cell K12 = (0.28).

if the value of cell J12 = .28 and the first letter of cell C12 is S, then
the value of cell K12 = 0.28.

I am stuck. Can someone shed some light?

eric
 
S

SteveW

Agreed Epinn

Its a Buy or Sell type spreadsheet
hence the need to see fi it starts with B or S
The values are *obviously* example ones.

Price = n.nn, Buy or Sell resulting in +n.nn or -n.nn

=IF(LEFT(C12)="B",-J12,IF(LEFT(C12)="S",J12,0))

as you posted.

Steve
 
B

Bob Phillips

=IF(C12="B",-J12,IF(C12="S",J12,0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
B

Biff

if it is better to use -J12 or J12*-1 in my formula.
Do you see any difference?
Click to expand...

No difference, but -J12 takes 2 less keystrokes than J12*-1.

Biff

Yes, I understand why you did it that way.

But I am thinking it may not be .28 for J13, J14, J15 ........ and Eric may
still want the numbers in column J show up in column K depending on "S" or
"B".

Now, he has two versions to choose from depending on his needs.

Biff, I am not sure if it is better to use -J12 or J12*-1 in my formula. Do
you see any difference? Feel free to fix up my formula if you see the need.

Thanks.

Epinn

Biff said:
Is it true that J12 can be any number other than .28?
Click to expand...

I read that to mean "a certain number" is specifically 0.28 which is why I
use AND.

Biff

Is it true that J12 can be any number other than .28? Can J12 be 0? Can
J12 be a negative number?

Assuming J12 is always > 0, in K12, key in this formula:-

=IF(LEFT(C12)="B",-J12,IF(LEFT(C12)="S",J12,0))

Format K12 accordingly.

Epinn

I am trying to get a cell to render a positive value or a negative value of
a
certain number if another cell has one of two letters. i.e.:

if the value of cell J12 = .28 and the first letter of cell C12 is B, then
the value of cell K12 = (0.28).

if the value of cell J12 = .28 and the first letter of cell C12 is S, then
the value of cell K12 = 0.28.

I am stuck. Can someone shed some light?

eric
 
Top