Saturday, August 18, 2018

yogi_Compute Days Elapsed In Column E Since Date Opened In Column I

Google Spreadsheet   Post  #2492

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

question by: Richie Mastria
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/sDDPYPlCXU4;context-place=forum/docs
How good are you at google sheets?
On summary page. Row BCG (A3). 3 days (B3).
I need a formula to calculate how many over 3 days and under 7 days excluding status "done".

The formula I have now to calulcate how many are over 3 days and under 7 is correct:
=countifs(BCGdays, ">3", BCGdays,"<7")

But unfortunately it counts ever one including ones that are done.

I need it to exclude BCGstatus (in name range) is "DONE".

Please help!

Thank you
Richie

yogi_Pull First Artist Name From Artist String In Column A Before Occurrance Of Any Of The Specified Conjunctions

Google Spreadsheet   Post  #2491

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

question by: Nick Fox SC
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/AO9f1CtJ4uk;context-place=mydiscussions
Extracting all words from a cell that occur before one of several given words
Hey everyone!

I'm trying to extract all words from a cell that occur before one of several given words. The use case for this is separating metadata to extract the first artist listed in a bunch of different complex artist strings. Below is my practical example:

Johnny Music 
Johnny Music featuring Timothy Muzak
Johnny Music, Timothy Muzak, Kevin Current
Johnny Music & Timothy Muzak feat. Kevin Current
Johnny Music feat. Kevin Current & Timothy Muzak
Johnny Music remixed by Kevin Current

In all cases, the result I want is 'Johnny Music'. 

I've tried a couple of LEFT, TRIM, and MID commands but they aren't letting me search for multiple different places to stop. For example:

=MID(A1;1;MIN(SEARCH("remixed by";A1&"featuring");SEARCH("remixed by";A1&"featuring"))-1)

=LEFT(A1;SEARCH({"remixed by";"&";"featuring"};A1)-1)

Both of these failed to SEARCH beyond 'remixed by', so they only extracted 'Johnny Music' in the last case, not in the other cases (featuring, etc were not located by the search. I got a '#VALUE!' error). 

Can anyone advise? Is there something obvious I am missing, or is this more complex than I first envisaged?

NOTE: This would be applied across multiple artist strings, so it's not as if all of them would start with 'Johnny Music' - therefore a simple LEFT with value parameters won't suffice.

Cheers everyone!


Friday, August 17, 2018

yogi_Multi Criteria Count -- Number Of Cars Available

Google Spreadsheet   Post  #2490

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-17-2018

question by: Richie Mastria
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/sDDPYPlCXU4;context-place=forum/docs
How good are you at google sheets?
On summary page. Row BCG (A3). 3 days (B3).
I need a formula to calculate how many over 3 days and under 7 days excluding status "done".

The formula I have now to calulcate how many are over 3 days and under 7 is correct:
=countifs(BCGdays, ">3", BCGdays,"<7")

But unfortunately it counts ever one including ones that are done.

I need it to exclude BCGstatus (in name range) is "DONE".

Please help!

Thank you
Richie




yogi_Recap Monthly Stats For Month Of June From Data For Weeks Of June

Google Spreadsheet   Post  #2489

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-17-2018

question by: Chris Ream
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/wT2M_iGJXzo;context-place=mydiscussions
Tab Formulas
I am trying to get data to rollover from multiple tabs. Ex from my doc. I want tabs 07/02,07/09,07/16 to have the sum of each cell go to the July recap tab. Please help. It would not let me upload as a google doc. I hope you get the idea. 
2018 Weekly Sales Columbus.xlsx
511 KB
2018 Weekly Sales Columbus.xlsx

Thursday, August 16, 2018

yogi_List In Cells A9:A Pay Period Dates Starting From Date In Cell B6 To End Of Month Of Date In B6

Google Spreadsheet   Post  #2488

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-16-2018

question by: Mobility Project PT
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/MndAN1XVBlQ;context-place=mydiscussions
Autofill dates for bi-monthly timesheet?
I have a timesheet to record worked hours. We have two pay periods that run from the 1st to 15th day of each month, and then from the 16th to the last day of the month. I have found a formula to autofill dates but can't figure out how to make it stop on the 15th (if starting on the 1st) or stop on the last day of the month (if starting on the 16th)

=ArrayFormula(ADD(A1,row(INDIRECT("A1:A"&16))))

What would you do? Thanks!

Wednesday, August 15, 2018

yogi_Compute Running Balance In Column C Given Deposits And Withdrawals In Columns A And B

Google Spreadsheet   Post  #2487

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-15-2018

question by: Elliana Lindsey
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/LdwPv_IZQMw;context-place=mydiscussions
=C1-B2+A2
C1-B2+A2 the result will appear on C2, how can i make it until 1000 column wihtout copy the formula until 1000 column?

yogi_Pull Into Cell C2 Unique Part Of Strings From Cells A2 And B2

Google Spreadsheet   Post  #2486

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-15-2018

question by: Krishna Goje
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/WjpB238Fnxc;context-place=forum/docs
Multiple strings comparison
Hello All,
I need a small help from you, what i want is compare the 2 different cells which has multiple strings and return the unique/mismatched values from both the cells.
Say:
CellA : Dilsukhnagar, Hyderabad, India
CellB : Gayathri Nagar, Hyderabad, India
Output : - Dilsukhnagar
         + Gayathri Nagar
Output must be in a single cell.
Any help will be appreciated.
Thanks,