Trouble with IF
 

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

Trouble with IF
 

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

What happens if C12 is neither B or S?

Ed Ferrero

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

Trouble with IF
 

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

"EG" wrote in message
...
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

Trouble with IF
 

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

"Ed Ferrero" wrote:

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

What happens if C12 is neither B or S?

Ed Ferrero

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

Trouble with IF
 

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

"EG" wrote in message ...
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

Trouble with IF
 

Is it true that J12 can be any number other than .28?
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 read that to mean "a certain number" is specifically 0.28 which is why I
use AND.

Biff

"Epinn" wrote in message
...
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

"EG" wrote in message
...
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

Trouble with IF
 

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" wrote in message ...
Is it true that J12 can be any number other than .28?
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 read that to mean "a certain number" is specifically 0.28 which is why I
use AND.

Biff

"Epinn" wrote in message
...
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

"EG" wrote in message
...
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

Trouble with IF
 

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

On Tue, 03 Oct 2006 08:44:48 +0100, Epinn
wrote:

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" wrote in message
...
Is it true that J12 can be any number other than .28?
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 read that to mean "a certain number" is specifically 0.28 which is why
I
use AND.

Biff

"Epinn" wrote in message
...
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

"EG" wrote in message
...
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

Trouble with IF
 

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

--
HTH

Bob Phillips

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

"EG" wrote in message
...
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

"Ed Ferrero" wrote:

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

What happens if C12 is neither B or S?

Ed Ferrero

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

Trouble with IF
 

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

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

Biff

"Epinn" wrote in message
...
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" wrote in message
...
Is it true that J12 can be any number other than .28?
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 read that to mean "a certain number" is specifically 0.28 which is why I
use AND.

Biff

"Epinn" wrote in message
...
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

"EG" wrote in message
...
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