CELL FORMAT - IF CONDITION

Suresh Kusunuri

Board Regular
Joined
Dec 28, 2007
Messages
124
Office Version
  1. 2007
Platform
  1. Windows
My Cell Format is ""0.00" Cr". But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other programme. Now, I want to get the value from this cell to another cell based on “Cr” by using IF condition. But “Cr” is not appeared in the cell for applying formula. Can anybody help me?

Thanks
Suresh
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,324
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
First all I don't know why you need all the quotes in your format as 0.00 "Cr" produces the same result, but getting back to your issue if the Cr is produced by a format then all it is doing is changing the appearance and not the value and so the formula can't pick up on it as it isn't really there.

Why can't you use the condition that makes you use the Cr in the formula i.e. if you want the cells that are positive then use >0 in your If formula?
 

loginid

New Member
Joined
Jun 5, 2015
Messages
8
HI
Rather do it by Formatting the cell.
press Ctrl + 1

Then go to Number>Custom>Type.......In the type area put # Cr
Note : # if no decimals
for Decimals put #.#

Hope it helps.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,324
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
@loginid, that doesn't answer the OP's question of how to detect the Cr in an if statement and #.# Cr will produce 123. Cr not 123.00 Cr as 0.00 does.
 

Suresh Kusunuri

Board Regular
Joined
Dec 28, 2007
Messages
124
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Thanks both of you for your reply. The statement is imported from another program. I din't format the cells. I required only Cr formatted cells for calculation.

Thanks once again.
Suresh
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,324
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
How is the cell being formatted to make the Cr appear by Conditional formatting or Custom formatting?
 

Suresh Kusunuri

Board Regular
Joined
Dec 28, 2007
Messages
124
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Hi Mr. Mark,

I think it's Custom Formatting. I am posting screenshot for your reference.

Thanks
Suresh
Format.PNG
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,324
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Yes that is Custom format and AFAIK we can't do anything to detect that with a formula (as I stated in post #2), if it was Conditional format then we could use the condition.
As the Cr appears only in certain cells what is significant about those cells?
Can you also post the formula that you are trying to use for the If statement?
 

Suresh Kusunuri

Board Regular
Joined
Dec 28, 2007
Messages
124
Office Version
  1. 2007
Platform
  1. Windows
Thank you,

My formula is =IF((RIGHT(C53,2)="Cr"),(C53+D53),""). It is getting Blank.

Suresh
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,324
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You still haven't stated what is significant about the cells with Cr in them. There must be a reason why they have Cr in them.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,940
Messages
5,525,764
Members
409,665
Latest member
littleriver

This Week's Hot Topics

Top