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
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
< 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