imvikky 0 Report post Posted August 2, 2006 Hi guys! I want a help from all those who have knowledge of excel. I have knowledge but am not conversant with excel sheets. What I want:- In an excel sheet I want to generate a monthly summary of a Mess... How I go about it:- I created 12 tabs or sheets named jan/feb/mar All sheets are numbered 1-31/28/30 depending on no of days in a month on the row 1 column one contains names of members of the mess Charges are a standard of 10 Rs for those who were present for the day... and Rs 0 for those who are absent.... I want to provide the user with an output of daily revenue generated as sum of all the rows corresponding to a coloumn and a monthly bill to be charged to a member of the mess as total of all columns corresponding to a row... I tried assigning a value 10 to constant P using Insert-Name-Define method but when I am tryin to do sum of two cells using this method Itis giving me error as #VALUE! How can I solve this using excel? Is there a method to import data from a (designated) cell of excel sheet to a MySQL database automatically... Such as monthly charges for a given person? or Daily revenue generated as on a day... Share this post Link to post Share on other sites
ani_meher 42 Report post Posted August 3, 2006 Why are you trying to define P as 10? just so that you should be able to sum it? If this is the case, you can use SUMIF condition instead. Insert a column containing all 10s for 31 days on a sheet. then use sumif command to get conditional sum for each day. SUMIF command is used to get conditional sum. In your case, you can use it to add 10 when the input in the bill is P. The sample formula will be SUMIF(B2:B32,"=P",Z2:Z32) where B column is containinga attendence for one member for whole month, and the Z column is containing all 10s. Hope that was helpful. Share this post Link to post Share on other sites
Puneet 0 Report post Posted August 3, 2006 Here is another approach. I have used the COUNTIF(range, criteria) function to count the number of Ps in a row as well as a column, and then multiplied this number by the AvgBill. You can either use naming a cell as "AvgBill" as I have done, or if the AvgBill varies by month you can use a fixed cell (e.g. $F$2) to compute, and fill in the value of AvgBill on top of every sheet. Naming the variable is applicable to all the sheets in an excel file. Am attaching a sample file, downlaod it and rename it to imvikky.xls. The site doesn't permits upload of xls files Hope that helps. As for importing data in MySQL, I guess that should be possible but am of no help there. imvikky.mp3 Share this post Link to post Share on other sites
sethuk 0 Report post Posted August 3, 2006 (edited) I have tried an alternate option for you. Since I am unable to attach the file here you could try the following steps. This is for a sample sheet. The format can be used for the reset of months. 1. Columns would be Days, Nos. 1 to 10, Total Present =countif(c24:l24,"P"), Revenue (=Total present * 10) 2. Days will be the same for the respective months. After the 31st row, total days person 1 present for the month =countif(c4:c34,"P"), bill amount to be presented for the particular month (no. of days * 10). 3. Use a conditional formatting for the 10 * 31 row/column matrix so that when you enter either "p" or "P" the particular row/column would be highlighted. Rest of the row/column would be left as blank. 4. Alternately highlight the blank row/column with a color to show that the person was absent. I think this should be better for now. Once you compute the bill/revenue for one month, you will be comfortable to compute for the next month. Puneet has tried the same thing and has done well. Good work. Hope this should be simpler one. Can any one tell me how to upload a xl file with a different extension? I tried to rename the file with a different extension but that was not possible. Edited August 3, 2006 by sethuk Share this post Link to post Share on other sites
ani_meher 42 Report post Posted August 3, 2006 Ya, puneet's way is good. Somehow i forgot about countif, and went on with sumif. @sethuk, for renaming extensions of files, in explorer, go to Tools->folder options->view. disable 'Hide extension for known file types' tickmark. Then you'll be able to see n rename the extentions of files Share this post Link to post Share on other sites
imvikky 0 Report post Posted August 3, 2006 Thank you ani thanks Puneet Thanks sethu... I think I would go ahead with Puneet's way cos in this I can easily change the daily charge at one place to change the whole bill.... Any Idea how to export data just from a few cells in a sheet to a PHP code or to a MySQL database... Their could be an alternate approach wherein one can ask a PHP script to read inputs from this xl sheet and feed it to database regularily but that would probably require a cronjob.... Anyone any help on this tooo Puneet thanks again Share this post Link to post Share on other sites
