sum function and then math

[chipmunken]

Hello and thanks for a great forum.

Here is what I want to do.

I have an excel file that has some formulas that dose some math I really want to put into my web based version of it.

Right now I'm stuck with being able to do math on a variable.

When I sum all the prices from a table and I print it it works fine, but when I want to do some math with what I have summed up I cant figure out how.

 

//this gets the prices and sums them up

$prissum = "SELECT SUM(pris) AS pris FROM sandra";

$sum = mysql_query($prissum) or die('Error, query failed');

 

//this prints the sum for one person and works fine.

print(mysql_result($sum, "pris"));

 

Now I have 3 values, the sum of all prices for 3 diffrent people.

 

Sandra      Tomas    Sebastian

600kr        700kr      900

800kr         

700kr

600kr

26kr

78kr

 

Sum:          Sum:        Sum:

2804          700        900

 

I'm going translate some formulas from my excel file so It will tell each person how much money they need to give eachother.

So I want to sum the summed prices and ofc do allot of other things math vise.

$sumbet_var = ($sum+$sum2+$sum3); // this is one thing I want to do

 

 

 

[JonnoTheDev]

$tables = array('sandra','tomas','sebastian');
$totals = array();
foreach($tables as $table) {
$prissum = "SELECT SUM(pris) AS pris FROM ".$table;
   	$sum = mysql_query($prissum) or die('Error, query failed');
$totals[$table] = mysql_result($sum, "pris");
}

// now you have the 3 totals
// array('sandra' => 2804, 'tomas' => 700, 'sebastian' => 900)
print_r($totals);

// do your math
$grandTotal = 0;
foreach($totals as $total) {
$grandTotal = $grandTotal+$total;
}
print $grandTotal;

[Mark Baker]

If you wanted, there's no need to rewrite your forumlas in PHP. You could read in the Excel workbook with all its formulas, populate the data cells and execute the formulas embedded in the Excel workbook using a PHP/Excel library

[chipmunken]

Thanks allot for help so far i have attached 2 pictures that I hope will cast some light on what I'm doing

This is project that I'm doing since I'm living with 2 other ppl. Plus I see it as an excellent way for me to learn.

 

 

$tables = array('sandra','tomas','sebastian');
$totals = array();
foreach($tables as $table) {
   $prissum = "SELECT SUM(pris) AS pris FROM ".$table;
      $sum = mysql_query($prissum) or die('Error, query failed');
   $totals[$table] = mysql_result($sum, "pris");
}

$math1=$totals['sandra'];
$math2=$totals['tomas'];
$math3=$totals['sebastian'];

$summath=($math1+$math2+$math3);
$divmath=($summath/3);

 

 

This while loop prints the list of prices you can see in my pictures

 while ($row = mysql_fetch_array($result_sa, MYSQL_NUM)){ echo "{$row[0]}kr <br />"; }

 

This prints sum of each person

print_r($totals['sandra']);
print_r($totals['tomas']);
print_r($totals['sebastian']);

 

Now comes the tricky part, I need to calculate what each person has to pay the others

It works in my excel document and there it looks like this

From what I understand from the excel formula its 3 if statements. If first is true then =0 if false then check next statement.

Sandra\\

Pay Tomas:

=IF(C24>=$C$29;0;IF(E24>=$C$29;IF(E24-$C$29>=$C$29-C24;$C$29-C24;E24-$C$29);D28))

Pay Sebastian:

=IF(C24+C25>=$C$29;0;$C$29-C25-C24)

 

Tomas\\

Pay Sandra:

=IF(E24+E25>=$C$29;0;$C$29-E25-E24)

Pay Sebastian:

=IF(E24>=$C$29;0;IF(G24>=$C$29;IF(G24-$C$29>=$C$29-E24;$C$29-E24;G24-$C$29);0))

 

Sebastian\\

Pay Sandra:

=IF(G24>=$C$29;0;IF(C24>=$C$29;IF(C24-$C$29>=$C$29-G24;$C$29-G24;C24-$C$29);0))

Pay Tomas:

=IF(G24+G25>=$C$29;0;$C$29-G25-G24)

 

Well this is how far I am atm. Now its time to make food. Later tonight ill continue.

Thanks for the help so far

If you wanted, there's no need to rewrite your forumlas in PHP. You could read in the Excel workbook with all its formulas, populate the data cells and execute the formulas embedded in the Excel workbook using a PHP/Excel library

Sounds nice I whoulde love to learn a bit more about that. Got any tips where to start reading? (havent googled yet)

 

[attachment deleted by admin]

[Mark Baker]

If you wanted, there's no need to rewrite your forumlas in PHP. You could read in the Excel workbook with all its formulas, populate the data cells and execute the formulas embedded in the Excel workbook using a PHP/Excel library

Sounds nice I whoulde love to learn a bit more about that. Got any tips where to start reading? (havent googled yet)

Reading my signature would give you a good place to start

[chipmunken]

haha sorry was in a bit of a hurry when I wrote the last post..

feel a bit foolish now

[chipmunken]

Well I solved it now. Thank you neil.johnson for your help. I must say I hade a great experience my first time on this forum.

 

Here is the translated excel formulas with rest of the code for the payment bit.

$tables = array('sandra','tomas','sebastian');
$totals = array();
foreach($tables as $table) {
   $prissum = "SELECT SUM(pris) AS pris FROM ".$table;
      $sum = mysql_query($prissum) or die('Error, query failed');
   $totals[$table] = mysql_result($sum, "pris");
						}

$math1=$totals['sandra'];
$math2=$totals['tomas'];
$math3=$totals['sebastian'];

$summath=($math1+$math2+$math3);
$divmath=($summath/3);

//--------------------- sandra payment ------------------------
if ($math1>=$divmath) { $sa_pay_to= 0; }
else { if ($math2>=$divmath) { 
							if ($math2-$divmath>=$divmath-math3) { $sa_pay_to=$divmath-$math1; }
							else { $sa_pay_to=$math2-$divmath; }
							}
	} 

if 	($math1+$sa_pay_to>=$divmath) {$sa_pay_se=0;}
else {$sa_pay_se = $divmath-$sa_pay_to-$math1;}

//--------------------- tomas payment ------------------------
if ($math2>=$divmath) { $to_pay_se= 0; }
else { if ($math3>=$divmath) { 
							if ($math3-$divmath>=$divmath-math3) { $to_pay_se=$divmath-$math2; }
							else { $to_pay_se=$math3-$divmath; }
							}
	} 
if 	($math2+$to_pay_se>=$divmath) {$to_pay_sa=0;}
else {$to_pay_sa = $divmath-$to_pay_se-$math2;}
//--------------------- sebastian payment ------------------------
if ($math3>=$divmath) { $se_pay_sa= 0; }
else { if ($math1>=$divmath) { 
							if ($math1-$divmath>=$divmath-math3) { $se_pay_sa=$divmath-$math3; }
							else { $se_pay_sa=$math1-$divmath; }
							}
	}
if 	($math3+$se_pay_sa>=$divmath) {$se_pay_to=0;}
else {$se_pay_to = $divmath-$to_pay_sa-$math3;}

[JonnoTheDev]

You may want to put some of the code into functions to avoid duplication.

 

i.e.

function payment($value) {
// code here
// return value
return $x;
}

// sandra payment
$sandra = payment($math1);

// tomas payment
$tomas = payment($math2);

 

You get the idea