Solved

If Statement Based on %

Posted on 2013-12-28
10
276 Views
Last Modified: 2013-12-28
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
Comment
Question by:Naresh Patel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 50
ID: 39743451
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
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39743464
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
 
LVL 11

Expert Comment

by:Gregory Miller
ID: 39743583
the formula is      
=Type(A2)

Open in new window


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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 40

Accepted Solution

by:
als315 earned 500 total points
ID: 39743593
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

Open in new window

Results of GetFormat(A1):
getformatGetFormat.xls
0
 
LVL 8

Author Closing Comment

by:Naresh Patel
ID: 39743985
excellent    sorry for delay in reply....thanks
0
 
LVL 81

Expert Comment

by:byundt
ID: 39743986
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
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39744018
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
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39744030
Sir Byundt,

will you please explain how it comes by your formula. I had tried to understand your formula but dint get it.

formula is working fine.


Thanks
0
 
LVL 81

Expert Comment

by:byundt
ID: 39744074
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
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39744407
Thanks for explanation.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question