Simple Logic Statement

[Ody]

Good day all,

I'm having some trouble with the following, seemingly simple, logic statement.

=IF(TEXT(CJ49/CE49,0%)<=1000,(TEXT(CJ49/CE49,0%)),"no")

I'm testing whether 2 cells, when divided, are greater than 1000%. In my test cells I have 300 and 1000 to make the math easy. 1000/300=333%, however my logic statement keeps replying "no".

Any thoughts here?

Thanks!

 

[Aladin Akyurek]

Good day all,

I'm having some trouble with the following, seemingly simple, logic statement.



I'm testing whether 2 cells, when divided, are greater than 1000%. In my test cells I have 300 and 1000 to make the math easy. 1000/300=333%, however my logic statement keeps replying "no".

Any thoughts here?

Thanks!

A2: 1000
B2: 300

=IF((A2/B2)*100<=1000,A2/B2,"no")

Format the formula cell as %.

Is this close?

 

[Scott Huish]

=IF(A2/B2<=10,A2/B2,"no")

 

[Dolemitefunk]

Or to minimize changes you can change your 1000 to 10 and then run. This is because 10 = 1000%, This way everything is in the same format.

 

[Ody]

Dolemitefunk: I thought of that and tried changing the 1000 to 10 but still got the same response.

Aladin Akyurek & HOTPEPPER: I will try that syntax for this problem however this little snippet is part of a larger logic function, which, in turn, is part of some VB so I may be stuck using the text() function to deal with formatting issues when I use my code.

Still, by all rights I think this should be working but it's not so I'm stuck.

 

[Scott Huish]

You shouldn't need the text function at all, VBA or not.

If a formula in the spreadsheet, just use the basic IF statement without the TEXT section.

If in VBA, do the math and then format the cell as percentage.

Sub test()
If Range("A2") / Range("B2") < 10 Then
    Range("C2") = Range("A2") / Range("B2")
    Range("C2").NumberFormat = "0%"
Else
    Range("C2") = "no"
End If
End Sub

 

[Ody]

Your solution makes sense. However I'm not sure how I would implement that into my existing code.

Here is my piece of code:

LastSheet = Worksheets(Worksheets.Count).Name
With Worksheets("Summary")lMaxRows = .Cells(.Rows.Count, "Q").End(xlUp).Row
.Range("Q" & lMaxRows + 1).Formula = "=if(iserror(concatenate(text('" & LastSheet & "'!BO49/ '" & LastSheet & "'!BJ49,""0%"") & "" / "" & char(10) & char(10) & '" & LastSheet & "'!BO51)),""N/A"",concatenate(text('" & LastSheet & "'!BO49/ '" & LastSheet & "'!BJ49,""0%"") & char(10) & char(10) & '" & LastSheet & "'!BO51))"

My problem is that when I concatenate 2 cells from the last page in my WB to one cell on my summary page, I lose the percent format of the first cell which is why I use the text() function. I'm trying to build another condition into this coding that will also show "N/A" if the percentage is greater than 1000.

I'm pretty sure I can do this with my current code however, I'm having trouble getting the logic to work for me, hence my first post.

Thoughts?

 

[tweedle]

TEXT Help:
Using the TEXT function converts a numeric value to formatted text, and the result can no longer be calculated as a number.

<TABLE style="WIDTH: 335pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=446 border=0><COLGROUP><COL style="WIDTH: 239pt; mso-width-source: userset; mso-width-alt: 5814" width=318><COL style="WIDTH: 96pt" width=128><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 239pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=318 height=20>=IF(TEXT(100,0%)<=1000,"Nice","What?")</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 96pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=128>What?</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>=IF(100<=1000,"Nice","What?")</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Nice</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>=IF(VALUE(TEXT(100,0%))<=1000,"Nice","What?")</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Nice</TD></TR></TBODY></TABLE>

 

[tweedle]

This Maybe:

.Range("Q" & lMaxRows + 1).Formula = "=IF('" & LastSheet & "'!BJ49/'" & LastSheet & "'!BO49 >10," & """N/A""" & ",'" & LastSheet & "'!BJ49/'" & LastSheet & "'!BO49)"
.Range("Q" & lMaxRows + 1).Style = "Percent"

 

[Ody]

Tweedle: I lost sight of the fact that text () makes the "number" useless for calculating. I also hadn't considered, or even thought of, using a value fx. Thanks for the help!