Excel 2003-How to make cell equal the sum of some other records where it is equal to a certain ID (or numbe?

Question

Basically, I have a few records in my excel table. One of the records has a field called "Average cost of Whole family holiday per ticket". This record is what I like to called the "master record". Other records link to this holiday are called "slave records" and these have a field called "link-to-master-record-id". In this field you put the same id as the ID of the master record. Now I want the field called "Average cost of Whole family holiday per ticket" in the master record, to be equal to the sum of all the slave records field "ticket cost" which have a link-to-master-record equal to the master record ID (which is 1).

I know it sounds confusing... For PHP and MySQL programmers, I will give the code to do this in PHP and MySQL, but then I need a way to do the same thing in Excel.

include 'connect.php';
$record = "1";
$query = "SELECT ticket_cost FROM holidayBookings WHERE `link-to-master-record` = '$record'";
$query = mysql_query($query);
$totalAvCost = 0; //Average cost of Whole family holiday per ticket
while($records = mysql_fetch_array($query)) {
list($ticketCost) = $records;
$totalAvCost = $totalAvCost + (int)$ticketCost;
}
$totalAmountOfRecords = mysql_num_rows($query);
$totalAmountOfRecords = (int)$totalAmountOfRecords;
$totalAvCost = $totalAvCost / $totalAmountOfRecords;
$totalAvCost = ceil($totalAvCost); //Not neccessary - just to make sure it is rounded to whole number, not neccessary for excel (i can just format the cell)
echo "Your Average cost per ticket is: £ $totalAvCost";
mysql_close($connection);
?>

Well, I hope that makes it clearer for PHP and MySQL programmers. Unfortunately, I can't use VBA for this or else it would be easier.

Thanks in advance.

Answer 1

Let's say your ID field is in column A and your cost field is in B. Row 1 contains the first master record. In your master record, type this in column C (note you can't use B again because it would create a circular reference:



=SUMIF(A:A,A1,B:B)



This means look in column A for records matching A1, then add up column B from the matching records. That gives you sum. To get an average you would use



=SUMIF(A:A,A1,B:B) / (COUNTIF(A:A,A1)-1)



The "-1" is to account for the presence of the master record. Note this will fail with a divide by zero error if there are no slave records. For the calculation to work properly there should be nothing in the cost field in the master record. Hope this helps.

Answer 2

often, i think we are ordinarily equivalent. approximately ninety 9% of each human is an identical. all of us have skill all of us have minds. yet there are some differences yet they don't seem to be too substantial. Racial differences first. The actual visual attraction is the main major. whilst human beings of all races finished the assorted intelligences quiz (greater that the curiously racially biased IQ attempt) diverse races scored bigger on diverse categories. Asians and whites have been ordinarily the wonderful in formal discovering, the type we enforce in college. Hispanics and Blacks are greater powerful and Kinesthetic discovering. nevertheless, those differences are very minor. In athletics, the diversities in muscle composition are additionally small with some communities faring greater powerful in some muscular categories than others. Many white international locations carry out greater powerful through fact the geography makes farming and cultivation undemanding, the muse of all society. maximum international locations and not applying a white majority are catching up. approximately monetary differences, i think of it quite is all as much as success. In persons, i think of a few human beings provide up some issues for yet another. seem at many extreme college activities gamers, many provide up intelligence for capability. Gender differences are additionally obvious. i think of ladies persons and men are equivalent intellectually, yet men, who've lots greater testosterone, are many times lots greater desirable. we are inching closer to equality everywhere. Africa, Asia, and South u.s., that are seen adverse, are at as quickly as becoming. women persons and men are gaining equivalent opportunities and racial differences are placing out to wane. many stuff in spite of the undeniable fact that, we are able to possibly by no skill have the skill to alter. ordinary, i think of we are ordinarily equivalent.

Answer 3

frequently, i've got faith we are generally equivalent. approximately ninety 9% of each and every human is the comparable. all of us have ability all of us have minds. yet there are some modifications yet they are no longer too substantial. Racial modifications first. The actual visual attraction is the main great. whilst human beings of all races executed the distinctive intelligences quiz (greater that the interestingly racially biased IQ attempt) distinctive races scored greater on distinctive categories. Asians and whites have been generally the suited in formal discovering, the style we enforce at college. Hispanics and Blacks are greater useful and Kinesthetic discovering. nonetheless, those modifications are very minor. In athletics, the modifications in muscle composition additionally are small with some communities faring greater useful in some muscular categories than others. Many white international locations carry out greater useful because of the fact the geography makes farming and cultivation easy, the inspiration of all society. maximum international locations and not making use of a white majority are catching up. approximately monetary modifications, i think of that's all as much as success. In persons, i think of a few human beings provide up some issues for yet another. seem at many intense college activities gamers, many provide up intelligence for capability. Gender modifications additionally are obvious. i think of females and adult adult males are equivalent intellectually, yet adult adult males, who've lots greater testosterone, are many times lots better. we are inching closer to equality everywhere. Africa, Asia, and South u . s ., that are called undesirable, are right away starting to be. females and adult adult males are gaining equivalent possibilities and racial modifications are placing out to wane. many stuff even with the undeniable fact that, we are able to probably on no account be able to alter. overall, i think of we are generally equivalent.