google sheets drop-down and calculus noob questions

Hey there!


looking at all those threads here I feel like I know nothing about spreadsheets
I apologize, I don't really use Excel that often anymore. (but I did 20 years ago very heavily and forgot EVERYTHING )
So, this might be super easy for you all - but for me, I can't even google my problems.

I made a little picture of what I need, and will try to explain it now:

googlesheetsissue.jpg

1. I need to make an "endless" chart, meaning I can add as many lines as I want (that sounds like it already is,but wait!)
2. The sum of what has been entered so far in each column should be displayed on top
3. I need some sort of drop-down option, so that every time I click on any cell, it will open the corresponing drop-down menu for that column
4. Is there any way to say like A=yes B=no C=maybe in a drop-down menu, but it will only show "A","B" or "C" in the cell after i chose it. (meaning when the cell itself is not selected, you only see the short "code")

EDIT HERE: only numerical values should be counted. If there is drop-down option for non numerical values, they don't need to be summarized on top.

5. not on the picture, but is there an easy way of calculating for example a mean value based upon what has been entered. I know how to do it when I know how many values I have - like having 5 values I say sum of all divided by 5. But what if it's a changing list.


thanks a lot for time and patience.

love and hugs from italy

1. You probably need to give some idea what "limitless" really means here. All spreadsheets I am aware of have limits on the size of a sheet, so they are generally not well suited to truly limitless data. I had trouble finding Google Sheets' limits, but it appears Sheets is limited to 5 or 10 million cells. So, for 5 columns, that means no more than about 2 million rows. I'm inclined to say that, if limitless means thousands of records, then a spreadsheet is okay. If limitless means millions of records, then you might want to consider something other than a spreadsheet for this.

2. In a spreadsheet, that sounds like a simple =SUM() function. Spreadsheet SUM() functions are usually good at ignoring text and blank cells, so something like =SUM(B3:B300003) (assuming limitless means never more than 300000 records) should work just fine.

3 and 4. I'm not all that familiar with how Sheets does data validation. My assumption is that it is similar to other spreadsheets where you define a list (usually in a block of cells elsewhere in the sheet), then refer the data validation to that block of cells. It also seems that data validation is not going to do well where the cell values are different from what is in the drop down menu. Exactly what you describe here might require macros to accomplish (I'm not sure there are many on this forum who are well versed in building macros for Sheets), which will make your spreadsheet much less compatible with other spreadsheet platforms.

5. Calculating a mean should be as easy as using an AVERAGE() function. Like SUM(), AVERAGE() is good at ignoring text and blank cells, so you can simply provide a larger range than you will ever need. =AVERAGE(B3:B300003) -- again assuming that you will never have more than 300000 records.

Does any of that help?