Hi Guys,

I am trying to figure out a formula in Excel. Basically I am trying to acheive the following for Ebay shop fees:

If an item is listed for 30 days between a set of values for the price, then the formula works like this:
=IF(T6*E6<=0,0,IF(T6*E6<=4.99*(AND(F6=30)),0.03,IF(T6*E6<=9.99*(AND(F6=30)),0.05,IF(T6*E6<=49.99*(AND(F6=30)),0.07,IF(T6*E6<=499.99*(AND(F6=30)),0.09,IF(T6*E6>=500*(AND(F6=30)),0.11))))))

Where F6 is the cell that can be 30 or 90 days. Now this works for 30 days, but I can't seem to add to the IF function for 90 days.

It will work like this, the bold section highlighted basically says If T6*E6 is less than or equal to 4.99 and F6 is 30 days, then the returned value will be 0.03. And so on with the returned value increasing as the T6*E6 value increases. But I need to add to this formula so that if F6 is changed to 90 days the returned value is increased accordingly. Simply duplicating the formulas, adding them to the end of this one, replacing the 30 with 90 and changing the returned values does not seem to work. Excel suggests that there is a problem with my AND function.

I need it to change the value returned if F6 changes to 90 days.

Can anyone help? It's a bit difficult to explain!

Did you try putting the greater "IF" (90) first in the formula? I have experienced cases where the first passing "IF" is what's reported. Simply put, the F6=30 statement is always true with all numbers over 30. Try making the formula test the higher option first.

Dave

I think the function is simply to long for excel.

Using:

=IF(T6*E6<=0,0,IF(T6*E6<=4.99*(AND(F6=30)),0.03,IF(T6*E6<=4.99*(AND(F6=60)),0.99,IF(T6*E6<=9.99*(AND(F6=30)),0.05,IF(T6*E6<=49.99*(AND(F6=30)),0.07,IF(T6*E6<=499.99*(AND(F6=30)),0.09,IF(T6*E6>=500*(AND(F6=30)),0.11)))))))

The above forumla works.

When I try the next step:

=IF(T6*E6<=0,0,IF(T6*E6<=4.99*(AND(F6=30)),0.03,IF(T6*E6<=4.99*(AND(F6=60)),0.99,IF(T6*E6<=9.99*(AND(F6=30)),0.05,IF(T6*E6<=9.99*(AND(F6=60)),0.98,IF(T6*E6<=49.99*(AND(F6=30)),0.07,IF(T6*E6<=499.99*(AND(F6=30)),0.09,IF(T6*E6>=500*(AND(F6=30)),0.11))))))))

It stops working and gives me your 'AND' error.

Looking on google gives me this info:

The maximum length of formulas (in characters)
Old Limit: 1k characters (Pre excel 2007)
New Limit: 8k characters (Excel 2007)

The number of levels of nesting that Excel allows in formulas
Old Limit: 7 (Pre excel 2007)
New Limit: 64 (Excel 2007)

So the latter formula has 255 characters so that would point to your immediate issue being you have to many nested functions.

You can certainly do this sort of thing using VBA or you could upgrade to excel 2007.

The VBA code would go something like this:



Dim ws As Worksheet

Public Sub ebay()

Dim i As Integer
Dim commi As Integer

' commi is short for comission which i guess you are calculating

i = 0
commi = 0

i = [T6] * [E6]

If [F6] = 30 Then

If i < 0 Then

commi = 0

End If

Else

If i < 5 Then

commi = 0.03

Else

If i < 10 Then

commi = 0.05

End If




End Sub



Obviously you have to include the rest of you conditions but you should be able to do that easily enough from what I have provided.

If you need anymore help just ask :)

The VBA code would go something like this:



Dim ws As Worksheet

Public Sub ebay()

Dim i As Integer
Dim commi As Integer

' commi is short for comission which i guess you are calculating

i = 0
commi = 0

i = [T6] * [E6]

If [F6] = 30 Then

If i < 0 Then

commi = 0

End If

Else

If i < 5 Then

commi = 0.03

Else

If i < 10 Then

commi = 0.05

End If




End Sub



Obviously you have to include the rest of you conditions but you should be able to do that easily enough from what I have provided.

If you need anymore help just ask :)

Thanks that is excellent, but unfortunately I am not familiar with VB! So it is a bit more work than I can probably acheive. My skills are currently limited to formulas in excel!

It's OK,

To simplify things I have one column working out the fee for 30 days and one column working out the fee for 90 days, using the COUNTIF function. So if 30 is entered in the duration one column does the calculation, then if 90 is enetered the next column does the calculation. I then have a third column, that uses the MAX function, to return the 30 day or 90 day insertion fee (whicheever is greater will be dictated by what is entered into the duration column, as the other will always be 0!). The first two columns are hidden and so I now have exactly what I wanted for the third column!

I hope this makes sense. Thanks to all for your input!:thanx:

Heres the VBA for it anyway.

Just click on the visual basic editor button and paste the following into it then you just press the run button to get it to output.



Dim ws As Worksheet

Public Sub ebay()

Dim i As Single
Dim commi As Single

' commi is short for comission which i guess you are calculating

i = 0
commi = 0

i = [T6] * [E6]

If [F6] = 30 Then

If i <= 0 Then
commi = 0
Else

If i < 5 Then
commi = 0.03
Else

If i < 10 Then
commi = 0.05
Else

If i < 50 Then

commi = 0.07
Debug.Print commi
Else

If i < 500 Then
commi = 0.09
Else

If i >= 500 Then
commi = 0.11
End If
End If
End If
End If
End If
End If

End If

If [F6] = 90 Then
If i <= 0 Then
commi = 0
Else

If i < 5 Then
commi = 0.03
Else

If i < 10 Then
commi = 0.05
Else

If i < 50 Then

commi = 0.07

Else

If i < 500 Then
commi = 0.09
Else

If i >= 500 Then
commi = 0.11
End If
End If
End If
End If
End If
End If

End If



[A5] = commi

End Sub

How about dividing the formula up into several cells. Have one cell calculate the result for 0<x<4.99, another cell calculate the value for 5<x<9.99 and a third cell calculate the value for x<10.

Then have a "master equation" that decides which of those to use. It takes a little more space, but you can troubleshoot each equation separately, rather than trying to get one huge equation to work. (I'm not sure I have your conditions down quite right, but you get the idea.)

If you want, you can always hide the extra columns to make it look nicer. :)



Tim F

How about dividing the formula up into several cells. Have one cell calculate the result for 0<x<4.99, another cell calculate the value for 5<x<9.99 and a third cell calculate the value for x<10.

Then have a "master equation" that decides which of those to use. It takes a little more space, but you can troubleshoot each equation separately, rather than trying to get one huge equation to work. (I'm not sure I have your conditions down quite right, but you get the idea.)

If you want, you can always hide the extra columns to make it look nicer. :)



Tim F

Thanks Tim,

Basically that is what I have done (see my above post), and then hidden the extra columns. It is probably not the most effcient way, but it works! I will look at the VB code Chergh has kindly posted and maybe try it out, as I would like to learn a bit more about VB.

Thanks again,