0

I have an Excel formula

=180*((B10/B9)*(((B10/B9)^2+((B12/B9)*2)^2)^-0,5)*BOOGTAN((((B10/B9)^2+((B12/B9)*2)^2)^-0,5))+((B10/B9)/WORTEL(1+((B12/B9)*2)^2))/(B10/B9)*BOOGTAN((B10/B9)/WORTEL(1+((B12/B9)*2)^2)))/(2*PI())

Which I converted to PHP

$straling = 180*(($gevelhoogte/$gevelbreedte)*((($gevelhoogte/$gevelbreedte)^2+(($afstand/$gevelbreedte)*2)^2)^-0.5)*atan(((($gevelhoogte/$gevelbreedte)^2+(($afstand/$gevelbreedte)*2)^2)^-0.5))+(($gevelhoogte/$gevelbreedte)/sqrt(1+(($afstand/$gevelbreedte)*2)^2))/($gevelhoogte/$gevelbreedte)*atan(($gevelhoogte/$gevelbreedte)/sqrt(1+(($afstand/$gevelbreedte)*2)^2)))/(2*PI());

But now, for some reason the outcome isn't equal. I expect it has somethingto do with (( vs )).

Can someone please help here?

Krooy_mans
  • 304
  • 3
  • 10
  • 2
    Try splitting everything up into different steps. then it should be easy to see where it goes wrong – ThomasVdBerge Jul 31 '15 at 07:58
  • 2
    `^` isn't the same operator in MS Excel as it is in PHP; in Excel, it's an exponential operator; in PHP it's a bitwise operator – Mark Baker Jul 31 '15 at 08:09
  • The hell means 'bitwise operator'? – Krooy_mans Jul 31 '15 at 08:33
  • You're also doing a lot of unnecessary calculations. For example `gevelhoogte/$gevelbreedte` and `$afstand / $gevelbreedte * 2`. It's better to store them in intermediate variables, and insert those in the calculation. That way you don't need so many parenthesis, and everything will be much easier to read (as well as being slightly faster). – Martin Tournoij Jul 31 '15 at 08:34
  • "Bitwise operators" mean operators that operate on the individual bits ("0" and "1"'s) of a number, rather than the whole numbers as such. You don't really need to know the details, but if you're curious [see wikipedia](https://en.wikipedia.org/wiki/Bitwise_operations) – Martin Tournoij Jul 31 '15 at 08:36
  • @Carpetsmoker fair point! Thank, and keep healthy without smoking the carpet! – Krooy_mans Jul 31 '15 at 08:39
  • computers use *binary* and works on *bits*. Therefore it's necessary to know [what bitwise operators are](http://stackoverflow.com/q/276706/995714) if you want to learn computer programming https://en.wikipedia.org/wiki/Bitwise_operation – phuclv Jul 31 '15 at 09:08

2 Answers2

2

You can't use ^ in PHP, as the ^ operator is the bitwise XOR operator in PHP. In order to use "to the power of" function, you need to use the pow(x,y) function in PHP, which gives "x to the power of y".

In short, you need to use this formula:

$straling = 180*(($gevelhoogte/$gevelbreedte)*(pow(pow($gevelhoogte/$gevelbreedte,2)+pow(($afstand/$gevelbreedte)*2,2),-0.5))*atan((pow(pow($gevelhoogte/$gevelbreedte,2)+pow(($afstand/$gevelbreedte)*2,2),-0.5)))+(($gevelhoogte/$gevelbreedte)/sqrt(1+pow(($afstand/$gevelbreedte)*2,2)))/($gevelhoogte/$gevelbreedte)*atan(($gevelhoogte/$gevelbreedte)/sqrt(1+pow(($afstand/$gevelbreedte)*2,2))))/(2*PI());
lowerends
  • 5,469
  • 2
  • 24
  • 36
  • Many thanks, however i don't understand how to interpret this. Can you please give a little example here? – Krooy_mans Jul 31 '15 at 08:33
  • `$gevelhoogte / $gevelbreedte) ^ 2` becomes `pow($gevelhoogte / $gevelbreedte, 2)`, as you're increasing `$gevelhoogte / $gevelbreedte` to the power of `2`. – Martin Tournoij Jul 31 '15 at 08:37
  • @lowerends, it doesn't work. it says: Fix [1/1] Syntax error, unexpected ',' on line 52 – Krooy_mans Jul 31 '15 at 08:45
  • @Krooy_mans, there were some misplaced brackets. Fixed it now in the formula. – lowerends Jul 31 '15 at 08:57
  • Wow, you have the eyes of a hawk! Thanks @lowerends, you really helped me out here and now the formule is equal to the Excel formule. – Krooy_mans Jul 31 '15 at 08:59
1

From your variable names I assume youre Dutch, but I'll keep this in English since other users will able to read then too ;)

Maybe you'll find this PHP library useful! http://phpexcel.codeplex.com/ There is a calculation module in there that will handle a lot of different ExcelFormulas.

Since your formula is quite complex, I am not sure if it will handle correct. But give it a try I'd say.

Xyv
  • 739
  • 7
  • 15