Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble formatting date | Excel Worksheet Functions | |||
Still having trouble | Excel Discussion (Misc queries) | |||
Trouble Forecasting | Excel Discussion (Misc queries) | |||
UGH! Trouble downloading, and opening anything | Excel Discussion (Misc queries) | |||
trouble shooting section | Excel Discussion (Misc queries) |