Convert this Equasion into Excel

[YoshiSato]

I have this IF statement I want to port into an excell spread sheet but I'm not sure how(I'm a C/VB programmer not a excel script kiddie) and I'm too lazy to install Visual Studio on my laptop to create this program in C#

IF B6 <= 99.99

{

(B3+B4) - (((B1+B2)+((B3+B4)*0.029) +0.3) + (B3+B4) *0.05)
}
IF B6 > 99.99 AND <= $1499.99
{
((B3 + B4) - 100) *0.025 + 5
}

If B6 > 1499.99
{
((B3 + B4) - 1500) *0.015 + 40
}


Any suggestions?

 

[NeoV]

you can nestle if statements in other if statements...

if(a=b,0,if(a=c,0,if(a=d,0,if(a=e,0,1))))

the format is still if(arguement,true,false)

 

[YoshiSato]

Came up with this.

Doesn't work

= IF(AND(B3>99.99,B3<1500),((B3+B4)-100)*0.025+5,0) IF(B3 <= 99.99, (B3+B4) - (((B1+B2)+((B3+B4)*0.029) +0.3) + (B3+B4) *0.05),0) IF(B3>1499.99,((B3+B4)-1500)*0.015+40,0)


I needed to change B6 to B3 but that's not the problem.

Each IF works by it's self just not when I have them all togeter.


Excel scripting SUCKS!

 

[NeoV]

you are putting a true and false in each if statement, which you don't want to have

you want
if(and(B3>99.99,B3<1500),((B3+B4)-100)*0.025+5,IF(B3 <= 99.99.....

you have to adjust your 2nd if string as well...make sense?

 

[theknight571]

How about this:

- Open your spreadsheet file
- Select Tools -> Macro -> Visual Basic Editor
- Select Intert -> Module
- Double Click on the New Module it created in the "Project Window"
- Paste the following code into the Module (HTML takes out the spacing, and I'm not sure how to keep it...the code is there it just doesn't look nice )

Public Function MyFunction(B1, B2, B3, B4, B6 As Double) As Double
Select Case B6
Case Is <= 99.99
MyFunction = (B3 + B4) - (((B1 + B2) + ((B3 + B4) * 0.029) + 0.3) + (B3 + B4) * 0.05)
Case 99.99 To 1499.99
MyFunction = ((B3 + B4) - 100) * 0.025 + 5
Case Is > 1499.99
MyFunction = ((B3 + B4) - 1500) * 0.015 + 40
End Select
End Function

- Save the worksheet

You should now be able to call the function from the worksheet. For example

=MyFunction(A1,A2,A3,A4,A6)

You can change the function name or variable names etc if you'd like. I stuck with B# so I could copy / paste the formulas you provided.



EDIT:

Got the If statement to work too.... I think... or at least the results match my funciton results from above.

=IF(B6<=99.99,((B3+B4) - (((B1+B2)+((B3+B4)*0.029) +0.3) + (B3+B4) *0.05)),IF(AND(B6>99.99, B6<=1499.99),(((B3 + B4) - 100) *0.025 + 5 ),IF(B6>1499.99,(((B3 + B4) - 1500) *0.015 + 40 ),"ERROR")))

 

[Kyteland]

Forget using AND(). Just do this:

F1 = (B3+B4) - (((B1+B2)+((B3+B4)*0.029) +0.3) + (B3+B4) *0.05)
F2 = ((B3 + B4) - 100) *0.025 + 5
F3 = ((B3 + B4) - 1500) *0.015 + 40

Excel formula: =IF(B6<=99.99,F1,IF(B6<=1499.99,F2,F3))