yogi_For Times In Minutes Seconds and Mili-Seconds Compute For Each Row Smallest (or Largest) Time

Google Spreadsheet   Post  #2337

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-31-2017

question by: Pierson raven

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/UgOj2k9VtL0;context-place=mydiscussions

How to sort times in minutes:seconds.milliseconds

I currently have times in as minutes:seconds.milliseconds, and need to find the lowest in each row, the formula works for times like 34.5 or 59.6 but sees any times with minutes such as 5:15.12 or 2:01.45 as 0's

yogi_A Row By Row Single Formula For Line Item Costs And SubTotals To Be Dragged Down For Copying

Google Spreadsheet   Post  #2336

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-31-2017

question by: Tom Stroller

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/UgOj2k9VtL0;context-place=mydiscussions

DYNAMIC SUMIF/SUBTOTAL RANGES

Hi there & Happy New Year to ya! :)

INVOICE SOLUTION:

Currently, I'm Filtering in only the line items (from another price reference sheet) which have a Qty filled in.

HOWEVER, as I edit these quantities, the resulting invoice ranges grow and shrink as items are Filtered in and out (both the # of rows, AND the # of groups that need 'SUBTOTALING' ), the range size changes so...

GOAL:

I'm trying to EITHER 1) Automatically resize the ranges (preferably the starting cell) of the SUMIF  so that it's always just below (or ON, if necessary) a row where COLUMN A is blank AND so that it's the FIRST blank (closest) above the SUMIF (that I've added text "SUB= $" to appear to be a Subtotal).

OR 2) SUM ALL numbers in the cells that say "Sub= $" (really it's a SumIF) so that I can SUBTRACT all previous 'Sub= $' values from the current 'Sub= $' cell. That way they will LOOK LIKE SUBTOTAL fxns without needing subtotal in a different column.

AND while 3) looking to keep the invoice looking lean & clear, by keeping all Balance Amounts & Subtotals in the same column.

PROBLEMS:

The two main issues I've run into are 

A) By putting the text, "Sub= $" into each Subtotaled item, I've made SUMIF tricky or impossible to use.

But I'd like to keep this text in there to keep the invoice looking simple; if possible.

B) NOT being able to SPLIT a range for input into a simple SUMIF.

C) Half the IF( solutions I've tried needs a RANGE value rather than my ArrayFormula workaround, so I'm stuck.

some of my thinking so far...

FXN-KNOWLEDGE THAT MAY BE NEEDED (any combination of these that works)

SUM( IF( INDEX( MATCH( SEARCH( SUMIF( MATCH( SUBTOTAL(?  QUERY(? ARRAYFORMULA(

ISTEXT( ISNUMBER( SPLIT(

SUBTOTALS:

I wouldn't mind using the actual SUBTOTALS fxn instead of SUMIF( "<>sub*" if that solves the problem.

STILL, I realize my #2 GOAL is counter-intuitive since SUBTOTAL fxn is designed to NOT count other subtotals, so we'll probably need some variation of Sum(If( since SumIf( doesn't seem to be working for this purpose.

OFFSET:
using OFFSET, I believe, would be too rigid, since it's not the same number of rows offset every time. 

SPREADSHEET (view only)

https://docs.google.com/spreadsheets/d/1X0A56KhoZVHdWf-pqtt6tgmHAnLhHqhLdwVTIDIq63I/edit?usp=sharing

I'm a musician, not a developer, so I can't (currently) wrap my head around google Scripts, So any way that this could be solved with a FXN in Sheets would be AMAZING.

Thank you THANK you!  :)

1 Automatically Reset Subtotal Ranges. Here's What it Should Look Like.png

229 KB

2 My Workaround = Grand Total minus the Sum of All previous subtotals .png

188 KB

3 Example to Work with.png

278 KB

yogi_Find The Last Populated Cell In A Row (or for that matter a column)

Google Spreadsheet   Post  #2335

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-30-2017

question by: Dave M.

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/eOATydopgDY;context-place=forum/docs

Returning the last populated value in a range

I can't believe I'm having such a hard time getting this

I need to return the last populated value in a range that starts at c13 and ends at g13

Any help would be very appreciated

yogi_Rearrange Start And End Times From DataBase Type Table Into Same Row

Google Spreadsheet   Post  #2334

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-30-2017

question by: bpugh2

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/e3Czpse4pCU;context-place=forum/docs

Start/end time questions: move to same row for calculations?

I have begun a timesheet where our school maintenance staff will use a form to enter their daily start/end times.

The start time is on one row in Form Responses 1; the end time is on another.

I (think I) need the start and end data to be on the same row for calculating total hours worked, etc.

My question is how can I group each employee's start and end time entries so that they are in a single row?

Please see sample sheet.

I thank you for your help!

https://docs.google.com/spreadsheets/d/1905ZtlsKlwfjHudZtrqTH33SbSTCwWG2bEUXG5aOdas/edit#gid=0

yogi_Set Up Custom Number Formatting To Show T for Trillion B For Billion and M For Million

Google Spreadsheet   Post  #2333

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-30-2017

question by: SharkSheaker

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/79kk_zvpguU;context-place=forum/docs

formatting of numbers not working

i want to realize to replace numbers like E3,E6,E9... with K,M,B...

i tried this formula: [>999999999]###.0,,," B";[>999999]###.0,," M"

adding a new case: [>999999999999]###.0,,,," T";[>999999999]###.0,,," B";[>999999]###.0,," M"

second line isnt working

can someone tell me why and help me to fix it.

yogi_Split And Sort List Of Words Into Separate Columns From And To Specified Letters

Google Spreadsheet   Post  #2332

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-29-2017

question by: MChu54321

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/dIpwgKKNcpc;context-place=forum/docs

Splitting range of cells based on first letter of word
Sample sheet with desired output/sample data: https://docs.google.com/spreadsheets/d/1r-ainbtI0ZLmPWR18CP2JvY9VwFZOl7HNAEg5MP4Ru8/edit?usp=sharing

Is there a formula that can be written to do the following?

Input: Unorganized range of cells with word (no numbers) values

Desired output: 2 ranges of alphabetized cells, the first with words starting with A-M, the second starting with N-Z.

yogi_Pull From Master Into Current Sheet Data For Current Week Plus Following 2 Weeks

Google Spreadsheet   Post  #2331

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-29-2017

question by: Jeremy

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/W0bdTQp7ymI;context-place=forum/docs

Referencing data from one sheet to another depending on date

Hi All,

Apologies if this has been asked before, I wasn't able to find exactly what I was after. I am keeping a rota for work colleagues and have a 'current' tab which shows only this week and the coming fortnight so that it is easy to navigate (particularly on iphones where it is regularly checked). I keep the master rota on a separate tab, which is where I update it. 

I'm wondering if it would be possible to have the current tab automatically pull the desired section of the rota depending on the date. At the moment I have to manually edit it each week to change the selection.

Thanks in advance for any suggestions,

Jeremy

https://docs.google.com/spreadsheets/d/12TkRSfy9MBPtpMpQSlOOo9iELoMIReSFcJvNdJtD7HU/edit?usp=sharing

yogi_Create Row By Row DropDown List Of Cities Based On Name Of State

Google Spreadsheet   Post  #2330

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-27-2017

question by: Chris

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/n4w_AIXnq20;context-place=forum/docs

Data Validation - Change Values Based On A Constant
Hi,

I am needing to create a Google Sheet that will allow users to use a drop down box to assign a value.  This drop down should be limited based on one of the columns that will be provided to the end user.

Here is a link to a quick mockup of what I am trying to do. https://docs.google.com/spreadsheets/d/1-wfhVpHNxIqAIbSv8KW1Ig48keP8axgnt1zzBgLPYJw/edit?usp=sharing   

(obviously not the real information but the same mechanism)

For example, if the person is listed as NJ, only NJ cities will show up in the drop down and not all cities.

I can not use any solution that involves scripting, as 95% of the end users will not have a Google account to log in with.  The end users are as a whole not very tech savvy so I need to make this as "idiot proof" as possible (they'll otherwise just type in whatever city they want for example, and make the exercise moot)

I've seen tutorials that either require scripting or use User Input A to change Dropdown B.  There will be dozens of people in this file at once, and 4-6,000 entries over 8-10 different files so I cannot do anything that requires manually setting each cell.

Is there an easy way to do this?  Thanks!

yogi_Compute Average Of Non-Zero Cells Of Every Nth Row In A Specified Range

Google Spreadsheet   Post  #2329

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-26-2017

question by: John Mahler

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/tQ_-7GYm-Xg;context-place=mydiscussions

Average every 19th cell, except when cell is zero
Hello all, 

I have searched the forum and I am unable to figure this out. Though there are seemingly answers out there, they do not work for my sheet.

I need to average every 19th row, starting with row 23, within a given column.  I have tried things that look like the following:

=averageif(IF(MOD(ROW(C23:C1500)-4,19)=0,C23:C1500),"<>0")

But that doesn't work. If I try to use the ArrayFunction operator, then I get an error.

I don't know how to link my sheet onto here so you can see my data.

Please help!

yogi_Compute Average Of Values In Column M (row 3 to 63) And Then Every 4th Column Indefinitely To The Right

Google Spreadsheet   Post  #2328

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-26-2017

question by: Jones Chevrolet

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/a-32RnH1Sd8;context-place=forum/docs

Multiple Cell Average Array

I currently have a spreadsheet (I will attach a copy) that is currently in order by years, and each year has 4 columns underneath it (Month to Date, % of Gross Month to Date, Year to Date, and % of Gross Year to Date). I have also created a section of 4 columns (columns E:H) that average up all previous years.

Is there a way to create an average array in E3 that does this for me instead of writing '=average(I3,M3,Q3,U3...)"? For example, I use the simple function "=ArrayFormula(if(E3:L11="","",minus(E3:L11,E15:L23)))" on a different sheet that works wonders subtracting values for an entire range for me.

At the end of every month, I currently add 4 more columns, import that data, and then have to add back in the I3, drag down the formula, reformat it, etc, and have to do so for multiple departments. Any help would be great appreciated!!!

Sample Spreadsheet

yogi_Compute Total Pay For Staff Based On Number Of Jobs Done On A Given Date

Google Spreadsheet   Post  #2327

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-25-2017

question by: Eric Steiner

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/gTHlZs3GY34;context-place=mydiscussions

Use dates and time in multiple cells and text in other cells to calculate a number.

Hi - I'm trying to use several pieces of data to obtain a number.  

To be more specific, if JIM (entered into a cell) works for me on DATE at TIME (entered into cells on the same row) and he only does one project, he's paid X (entered into another cell on the same row).  If he works for me on the same date and time but does a second project (project details are entered on an adjacent row down), he's paid Y, etc.  The additional rows on which Jim works should be filled with a dash (-) as the total number will only be entered on one cell.    

https://docs.google.com/spreadsheets/d/1YZrHbBatOA-zuXq1bGcIFE9QQ0sisOD9O_y_HOlKP5Y/edit?usp=sharing

1 row = 1 job which pays $750, 2-3 jobs at the same time and date pays $850, 4-5 jobs at the same time and date pays $900, and 6 pays $1,000.

Any ideas?     

yogi_Flatten Row By Row multiple rows with non-overlapping values in columns into one row by subject

Google Spreadsheet   Post  #2326

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-24-2017

question by: Vladimir

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/mXaiPDQN3JY;context-place=mydiscussions

How to "flatten" rows? (multiple rows with non-overlapping values in columns into one row)

Hello,

I would like to ask for your help

1) I want to collect survey responses from Google, with different departments filling out different sections of the form (so in the data collection one row represents the answers of different departments, the answers do not overlap between departments). There will be multiple subjects with the same departments answering the questions.

2) I need to combine the rows (answers from all departments) for the given subject (kind of "flatten" the rows combining the answers from multiple departments into one row (one row for each subject).  All I can think of right now is to create multiple sheets (one for each subject), run a query like 
 " select * where B = 'ZLK' order by A "  and then add one row with JOIN()  (which is "kind of" doable, but requires additional sheets (number equal to number of subjects) and lot of manual input

Example datasheet
https://docs.google.com/spreadsheets/d/1PR2Vh4i2DeV7QiZi9znOxaY3vxabJF196wvWY_gROQs/edit?usp=sharing

yogi_Find Row By Row Column Header For Non-Empty Cell In Specified Range

Google Spreadsheet   Post  #2325

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-21-2017

question by: lezook

https://productforums.google.com/forum/#!topic/docs/RtqoI2_5UPc;context-place=topicsearchin/docs/category$3Aspreadsheets%7Csort:relevance%7Cspell:false

How to find last non-empty cell in row and return it's column header

https://docs.google.com/spreadsheets/d/1iF8h1BB59Kd80Mp_E1w0cVXO6lt0sbBIZO_S8C2erPo/edit?usp=sharing

First-time poster.
I'm trying to find the last non-blank cell in a row and return it's column header.  See link to sample spreadsheet.
I was able to do this just fine in Excel, but it doesn't seem to work in Google Sheets.

Any assistance is sure appreciated!

yogi_Combine Row By Row Non-Blank Entries In Columns A to D With A Hyphen

Google Spreadsheet   Post  #2324

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-18-2017

question by: Ryan

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/34iQzzOcSfY;context-place=forum/docs

Conditional Concatenate Down a Column

Alright community, I'm conceding that I may have been out of the GSheets game for too long and have lost the edge!

I have a spreadsheet where some columns have numbers and alphanumeric sets, and some are blank. I'd like to concatenate down the whole column, adding a hyphen between each, but only if there is data in the cells. The function will be implemented on a Form Response sheet so I don't want to have to autofill down the column. I'd like to just have one function.

I can easily do this with =ARRAYFORMULA(A2:A&"-"&B2:B&"-"&C2:C&"-"&D2:D) but of course it will add unnecessary hyphens when there are blank cells in a row.

So I tried this in my working sheet...

=IF(AND(ISBLANK(J3)=FALSE,ISBLANK(K3)=FALSE,ISBLANK(L3)=FALSE,ISBLANK(M3)=FALSE),J3&"-"&K3&"-"&L3&"-"&M3,IF(AND(ISBLANK(J3)=FALSE,ISBLANK(K3)=FALSE,ISBLANK(L3)=FALSE,ISBLANK(M3)=TRUE),J3&"-"&K3&"-"&L3,IF(AND(ISBLANK(J3)=FALSE,ISBLANK(K3)=FALSE,ISBLANK(L3)=TRUE,ISBLANK(M3)=TRUE),J3&"-"&K3,"")))

...Which works but I have to autofill down the whole column. So then I tried this...

=IF(AND(ISBLANK(J3:J)=FALSE,ISBLANK(K3:K)=FALSE,ISBLANK(L3:L)=FALSE,ISBLANK(M3:M)=FALSE),ARRAYFORMULA(J3:J&"-"&K3:K&"-"&L3:L&"-"&M3:M),IF(AND(ISBLANK(J3:J)=FALSE,ISBLANK(K3:K)=FALSE,ISBLANK(L3:L)=FALSE,ISBLANK(M3:M)=TRUE),ARRAYFORMULA(J3:J&"-"&K3:K&"-"&L3:L),IF(AND(ISBLANK(J3:J)=FALSE,ISBLANK(K3:K)=FALSE,ISBLANK(L3:L)=TRUE,ISBLANK(M3:M)=TRUE),ARRAYFORMULA(J3:J&"-"&K3:K),"")))

...Which pretty much yielded desired results except that it only formats based on the first row of data it sees so the rest of the column is only correct in the instances that the row's contents match the first row of data.

Anyway, I'm stumped and need some help. I saw this post but I don't really understand the construction of the solution and therefore don't know if it will apply to my sheet.

Thanks in advance!