Solved: more if condition in one formula

[danovkos]

Hi all,
pls. how can i do the formula with more conditions?
i use this, but i need add more conditions as that.

=IF(H4=800;"800";IF(H4>=700;"700";IF(H4=600;"600";IF(H4>=500;"500";IF(H4>=400;"400";IF(H4>=300;"300";IF(H4>=200;"200";IF(H4>=100;"100";1))))))))

thx

[nst1107]

Take a look at post #3 in this thread: http://www.vbaexpress.com/forum/showthread.php?t=25328

[danovkos]

YES, it can help me. But i belived, that exist some format of formula, where is something like this
formula (command(if(A1<1,A1<5,A1<10...);(first, second, third)

and it will do
if is cell A1<2 THEN return "first"
if is cell A1<5 THEN return "second"
if is cell A1<10 THEN return "third"

i have thing, i saw it somwhere :( but i dont know where :(

do you know about something like that?

[nst1107]

Unfortunately, I've never seen anything like that. However, see if using =FLOOR(H4,100) doesn't solve your delimma.

[danovkos]

it is now the rule, that all my data go alway to down...its many of conditions :( but thx.

any other sugustions?

[tpoynton]

what you describe seems more like a frequency formula? look it up in help. Posting a sample workbook with desired outcome would help too.

[danovkos]

no, frequency i know but this is not what i need
nevermind, thank you

[AZIQN]

Hi danovkos, it looks like all you are trying to do is round any three digit number down to the nearest 100th value, and if it's less than 100 you want to display a "1". I wrote a simple macro that should accomplish this without using multiple nested IF statements. Hopefully this is what you are looking for...


Sub RoundDown()
Range("H4").Select
If Len(Cells(4, "H").Value) = 3 Then
Cells(4, "I").Value = Left(Cells(4, "H").Value, 1) & "00"
Else
Cells(4, "I").Value = "1"
End If
End Sub

If you are looking to fill this formula down column H, use the following code instead:

Sub RoundDown()
Application.ScreenUpdating = False
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
For i = LastRow To 1 Step -1

If Len(.Cells(i, "H").Value) = 3 Then
.Cells(i, "I").Value = Left(.Cells(i, "H").Value, 1) & "00"
Else
.Cells(i, "I").Value = "1"
End If
Next i
End With
End Sub

[Krishna Kumar]

Create a table and try LOOKUP function.

for e.g. M3:N13 houses the following


-9.9999E+307 0
0 1
100 100
200 200
300 300
400 400
500 500
600 600
700 700
800 800
801 1

and try,

=LOOKUP(H4,M3:N13)

HTH

[AZIQN]

If you do not want to use VBA you may be able to use the SUMPRODUCT function...

[danovkos]

thank you all for help
all of your info i can use...it is perfect..thank you very much to all