Skip to Content.
Sympa Menu

market-farming - Re: [Market-farming] spreadsheet question - Crop Planning for Organic Vegetable Growers

market-farming AT lists.ibiblio.org

Subject: Market Farming

List archive

Chronological Thread  
  • From: "Rob Wallbridge" <rob AT songberry.ca>
  • To: "'Market Farming'" <market-farming AT lists.ibiblio.org>
  • Subject: Re: [Market-farming] spreadsheet question - Crop Planning for Organic Vegetable Growers
  • Date: Mon, 10 Feb 2014 22:56:11 -0500

Hi Beth,

 

I think I understand what you’re trying to accomplish, but I can’t make the “SUMPRODUCT” function work without entering 62 arrays so try this (if it’s not already there, you’ll need a blank column next to each week’s quantity column for the “weekly totals”):

1)     Enter the product formula in one cell; if you want the “c” column to be a constant reference, put a $ in front of it (e.g. in e5 enter “=$c5*d5”)

2)     Copy this cell.

3)     Highlight the rest of the cells in this column where you want the same formula (e.g. cells e6 thru e62). Hit “paste”. This should automatically generate results for c6*d6 thru c62*d62 in cells e6 thru e62.

4)     At the bottom of the column (e63) hit “=” then click on “SUM” at the top. The window should read =SUM(E5:E62). If not, edit it. This should give you the sum of the products.

5)     Highlight this entire column (e5 thru e63), copy it, then paste it in the column to the right of the other 51 weeks (your weekly totals). You can speed this up by highlighting the first cell in your next “weekly total” column, holding down the “Ctrl” key and highlighting the first cell in every “weekly total” column, then hitting paste. This should give you all the results you need.

 

I hope this explanation is clear and gives you what you’re looking for – if not, feel free to let me know!

 

Cheers,

Rob

 

 

From: market-farming-bounces AT lists.ibiblio.org [mailto:market-farming-bounces AT lists.ibiblio.org] On Behalf Of Beth Spaugh
Sent: Monday, February 10, 2014 3:36 PM
To: 'Market Farming'
Subject: [Market-farming] spreadsheet question - Crop Planning for Organic Vegetable Growers

 

I have my old planning spreadsheets, but am trying to make time to use these new ones since we are changing a lot of things this year, like opening a farmstand.  The spreadsheets are designed more for paper use (a LOT of paper), but I am automating in Excel 2003.  I know there has to be a formula for this one, but I am coming up blank.

 

I need to sum the products of two columns. The columns are value (column C) and quantity (Column D).  62 rows of crops. 

 

If I get one working, and can drag and correct if need be – Column C, the value stays constant across different quantity columns (weekly)

 

Appreciate help. Its been so long since I’ve done much of this that I am rusty.  Have been trying SUM, PRODUCT, SUMPRODUCT, etc.  colons, commas.  I’m probably just so rusty I’m missing something obvious.  I did manage to get the values from two worksheets merged into a third – that was simple and I thought I was on a roll, but have hit a definite roadblock.  With year-round sales I don’t want to manually enter (c5*d5)+(c6*d6)through c62*d2), and then do it for 52 columns.  I know there has to be an easy way.

 

Thank you.

 

Beth Spaugh

Rehoboth Homestead, Peru NY

http://rhomestead.com      http://www.facebook.com/rhomestead

 

"How we eat determines, to a considerable extent, how the world is used." - Wendell Berry

"Without power over our food, any notion of democracy is empty." - Frances Moore Lappe

It's not that nice guys finish last. They have a whole different notion where the finish line is. - unknown

 

 

 




Archive powered by MHonArc 2.6.24.

Top of Page