Page 1 of 2

Convert excel formula to php

Posted: Fri Aug 26, 2005 5:28 pm
by Jim_Bo
Hi,

I have made an excel formula using if statment to add a % to a figure depending on how high it is, can the following excel formula be created in php easily so the final figure can be created on the fly within my php script rather than creating sql dump file from excel file?

Code: Select all

=IF(D11<50,D11*0.4,IF(D11<100,D11*0.3,IF(D11<250,D11*0.2,IF(D11<500,D11*0.1,IF(D11<750,D11*0.1,IF(D11<1000,D11*0.1,IF(D11<2000,D11*0.06,D11*0.05)))))))+D11

Cheers

Posted: Fri Aug 26, 2005 5:37 pm
by feyd
here's a stab

Code: Select all

if($value < 50)
  $value *= 1.4;
elseif($value < 100)
  $value *= 1.3;
elseif($value < 250)
  $value *= 1.2;
elseif($value < 500)
  $value *= 1.1;
elseif($value < 750)
  $value *= 1.1;
elseif($value < 1000)
  $value *= 1.1;
elseif($value < 2000)
  $value *= 1.06;
else
  $value *= 1.05;
your entire equation is taken into account with that, although you could easily remove the 1.1's up to 1000, but I figure you're planning to change that scaling at some point.

Posted: Fri Aug 26, 2005 5:46 pm
by Jim_Bo
Hi,

You have

if($value < 50)
$value *= 1.4;

i assume that

if($value < 50)
$value *= 0.4;

is what I am after?

or should it be 1.4 to get the result I need?



Cheers

Posted: Fri Aug 26, 2005 5:49 pm
by s.dot
I figure he misread it and the 1.something should be 0.something.

Posted: Fri Aug 26, 2005 6:01 pm
by feyd
no.. it should be 1.4. Your equation adds the value to the percentage:

(value * 0.4) + value = value * 1.4

Posted: Fri Aug 26, 2005 7:06 pm
by Jim_Bo
Hi,

Doesnt seem to be working very well, using:

Code: Select all

if($rate2 < 1) 
  $rate2 *= 1.1; 
elseif($rate2 < 100) 
  $rate2 *= 1.2;
elseif($rate2 < 250) 
  $rate2 *= 1.3; 
elseif($rate2 < 500) 
  $rate2 *= 1.4; 
elseif($value < 750) 
  $rate2 *= 1.5; 
elseif($rate2 < 1000) 
  $rate2 *= 1.6; 
elseif($rate2 < 2000) 
  $rate2 *= 1.7;
when I set rate2 to the value 99 I get a result of 118.8 but I should be getting 108.9

when I set rate2 to the value 150 I get a result of 195 but I should be getting 180

?

Cheers

Posted: Fri Aug 26, 2005 7:15 pm
by feyd
the code is acting as you laid it out to. 99 falls under 100, 1.2 is used.

Posted: Fri Aug 26, 2005 7:21 pm
by Jim_Bo
Hi,

Doh just found that out :oops:

Whats the cleanest way to add another 10.5% to the total that was created with the formula, then round up to the nearest full number?


Thanks

Posted: Fri Aug 26, 2005 7:24 pm
by feyd
$value = ceil($value * 1.105);

Posted: Fri Aug 26, 2005 7:33 pm
by Jim_Bo
Hi,

Thanks for your help!

Posted: Fri Aug 26, 2005 10:11 pm
by Jim_Bo
Actually .. I am a little lost

Code: Select all

if($rate2 < 1) 
  $rate2 *= 1.1; 
elseif($rate2 < 100) 
  $rate2 *= 1.2;
elseif($rate2 < 250) 
  $rate2 *= 1.3; 
elseif($rate2 < 500) 
  $rate2 *= 1.4; 
elseif($value < 750) 
  $rate2 *= 1.5; 
elseif($rate2 < 1000) 
  $rate2 *= 1.6; 
elseif($rate2 < 2000) 
  $rate2 *= 1.7;
the above code isnt correct what I am tring to acheive as its making ..

if $rate2 is more than1 but less than 100 add 10% (so 99 should be adding 10% not 20% & 150 is is adding 30% rather than 20% like I need)

It should be along the lines of:

if rate2 is more than 1 but less than 100 add 10%

elseif rate2 is more than100 but less than 250 add 20%

elseif rate2 is more than 250 but less than 500 add 30%

and so on ...

I guess it may also need to be 1-99, 100-249, 250-599 .. rather than 1-100, 100-250 .....

Looks like the formula above isnt creating what im after?


Thanks

Posted: Fri Aug 26, 2005 10:21 pm
by feyd
seems you forgot basic math symbols :lol:

< is less than.
> is greater than.

here's a start:

Code: Select all

if($value < 1); // do nothing
elseif($value < 100)
  $value *= 1.1;
that translates to, if $value is less than 1, do nothing. If it's less than 100, add 10%.

Posted: Fri Aug 26, 2005 10:26 pm
by Jim_Bo
Whats the best way to write it to create it as I need, like the excel formula that I put together in the first post.

I assume there could be quite a simple way to create the entire formula so there is on if/elseif for each statement


Cheers

Posted: Fri Aug 26, 2005 10:39 pm
by feyd
well.. you have to do a comparison somewhere, but you could use an array to simplify it

Code: Select all

$adjustor = array(1=>1, 100=>1.1, ...);
foreach($adjustor as $limit => $mul)
{
  if($value < $limit)
  {
    $value *= $mul;
    break;
  }
}

Posted: Sat Aug 27, 2005 3:04 am
by Jim_Bo
This looks to work using:

Code: Select all

if ($rate2 >= 1 and $rate2 <= 99)
	$rate2 *= 1.1;
	
elseif ($rate2 >= 100 and $rate2 <= 249)
	$rate2 *= 1.2;

elseif ($rate2 >= 250 and $rate2 <= 499)
	$rate2 *= 1.3;
Does that look plausable?


Thanks