CELL FORMAT - IF CONDITION

Suresh Kusunuri

Board Regular
Joined
Dec 28, 2007
Messages
138
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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?
 
Upvote 0
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.
 
Upvote 0
@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.
 
Upvote 0
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
 
Upvote 0
How is the cell being formatted to make the Cr appear by Conditional formatting or Custom formatting?
 
Upvote 0
Hi Mr. Mark,

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

Thanks
Suresh
Format.PNG
 
Upvote 0
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?
 
Upvote 0
Thank you,

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

Suresh
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,700
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top