Convert excel formula to php

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Convert excel formula to php

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post 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
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

I figure he misread it and the 1.something should be 0.something.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

no.. it should be 1.4. Your equation adds the value to the percentage:

(value * 0.4) + value = value * 1.4
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

the code is acting as you laid it out to. 99 falls under 100, 1.2 is used.
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

$value = ceil($value * 1.105);
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

Hi,

Thanks for your help!
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post 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
Last edited by Jim_Bo on Fri Aug 26, 2005 10:21 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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%.
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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;
  }
}
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

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