• Status: Solved
• Priority: Medium
• Security: Public
• Views: 284

# If Statement Based on %

Hi Expert,

Any one know how to calculate result based on data of the Cell.
let say in Cell A2 there is numeric value 10 then I want result in cell A1 = "Number", if in cell A2 there is 10% then result in Cell A1 = "Percentage".

Thanks
0
Naresh Patel
1 Solution

Microsoft MVP ExcelCommented:
Hello,

are you talking about the formatting of a number? A cell holds a value only. The formatting determines if it is displayed with decimals or without, or as a percentage. So, the value 0.1 can be formatted to show as 10%, but the numeric value is still 0.1

There is no in-built function in Excel to evaluate the formatting of a cell.

What are you trying to achieve? Can you explain the bigger picture?

cheers, teylyn
0

Hi teylyn,

task is simple if cell have number value then I want this text as a result  "Amount". and if cell have percentage value i.e. exactly like this "10%" then I want this text as a result "Percentage".

Possible....?

Thanks
0

General ManagerCommented:
the formula is
``````=Type(A2)
``````

The return is a numeric value that represents the type of content:
Number=1
Text=2
Boolean=4
Error=16
Array=64

I am not certain you can do more than that without writing a much more detailed formula to inspect the contents of the cell.
0

Commented:
You can add your own function, where you can do it. For example, it could be:
``````Function GetFormat(Cell As Range) As String
Dim F As String
F = Cell.NumberFormat
If InStr(F, "%") Then
GetFormat = "Percentage"
ElseIf IsNumeric(Cell) Then
GetFormat = "Amount"
Else
GetFormat = "Other"
End If
End Function
``````
Results of GetFormat(A1):
GetFormat.xls
0

excellent    sorry for delay in reply....thanks
0

Commented:
You can return information about cell formatting with CELL("format",A2). This suggests using a formula like:
=IF(NOT(ISNUMBER(A2)),"Text",IFERROR(INDEX({"Amount","Percentage","Date"},MATCH(LEFT(CELL("format",A2),1),{"C","P","D"},0)),"Number"))

This formula will return Text if the cell contains text, Amount if it contains currency formatted value, Percentage if it contains a percent, Date if it contains a date or time and Number for all other numeric values.
0

Sir Byundt,

Thank you very much for posting solution in solved & accepted question. my first priority to get it done by formula only rather then VBA code. but I thought VBA is only way out. This is what I am looking for.

But I am also give full marks to Mr.als315 as his solution is fulfill my criteria at that point of time.

Thank you both of you
0

Sir Byundt,

formula is working fine.

Thanks
0

Commented:
CELL("format",A2) returns a short code based on the number format of cell A2
General         "G"
#,##0         ",0"
0.00          "F2"
#,##0.00           ",2"
\$#,##0_);(\$#,##0)         "C0"          other currency formats return values beginning with C
0%          "P0"       other percentage formats return values beginning with P
m/d/yy or m/d/yy h:mm or mm/dd/yy         "D4"     other date & time formats return values beginning with D

I used LEFT to return the first character from the value that CELL("format",A2) returned.

MATCH(LEFT(CELL("format",A2),1),{"C","P","D"},0)      compares that character to C, P and D (for Currency, Percentage and Date/Time).

INDEX({"Amount","Percentage","Date"},MATCH(LEFT(CELL("format",A2),1),{"C","P","D"},0))  INDEX uses the value from MATCH to choose between Amount, Percentage and Date.

IFERROR(INDEX({"Amount","Percentage","Date"},MATCH(LEFT(CELL("format",A2),1),{"C","P","D"},0)),"Number")    IFERROR returns Number if MATCH wasn't satisfied. This covers the case of General, Fixed (floating point), scientific notation and fractions.

IF(NOT(ISNUMBER(A2)),"Text", ...    returns Text if A2 does not contain a number

Here is the resulting complete formula:
=IF(NOT(ISNUMBER(A2)),"Text",IFERROR(INDEX({"Amount","Percentage","Date"},MATCH(LEFT(CELL("format",A2),1),{"C","P","D"},0)),"Number"))
0