Monday, May 14, 2018

yogi_Count Entries For Specified TimeFrames

Google Spreadsheet   Post  #2447

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     May-11-2018
Sample Sheet

The timestamps are taken from a Google Form, rest of the data redacted for privacy.

I would like to count the number of entries i have in the current month, using 'TODAY()' so the sheet stays updated as the months pass and more form entries are submitted.  I would also like updated totals of the three months and six months including the current month.  I have reworked a formula(I do not understand this formula at all )taken from elsewhere on this forum but it fails when the date range spills over to the previous year.  On the 'Totals' tab, I have my best guess in column B and the expected Values in D. You can see that December returns zero.  I am open to alternative methods, especially if they are simpler/more readable.

Please let me know if there is more information I can provide.  Thanks in advance.

Friday, May 11, 2018

yogi_List WeekDays (Monday to Friday) Only Between From And To Specified Dates

Google Spreadsheet   Post  #2446

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

Hi everyone,

What I'm looking to do is simply list the dates for an entire year (Jan 1, 2018 through Dec 31, 2018) in the format "Friday, May 11" excluding weekends. That's all...

Similar to this:


yogi_From 'Speed Torque Table' Sheet2 Find The Torque Associated With Specified Speed Value In Cell A3

Google Spreadsheet   Post  #2445

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

question by: Tyler Toepke-Floyd
https://productforums.google.com/forum/#!topic/docs/2LCjOMYaI6w;context-place=forum/docs

How to get sheets to pick a value from a table that is closest to a target value in Sheets

I have a table that contains testing data values for speed and torque of a motor. I am doing a calculation to give me a calculated speed. I want to make a formula that will find the speed closest to my target value, and then grab the torque that is associated with that speed. The goal is to find the actual attainable torque based on a theoretical speed. 

Target speed 1000 RPM

table in separate sheet
Speed     Torque
980           21
997           19
1005         18
1012         16

I would want the cell to display 19 as the answer given the input of 1000. 

So its looking up a value closest to the target value and then grabbing the number from the cell beside it. Hope this makes sense

Wednesday, May 9, 2018

yogi_Sort By 'Day And Time' When 'Day And Time' Together Have Been Entered As Text

Google Spreadsheet   Post  #2444

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

question by: ScriptTrouble
https://productforums.google.com/forum/#!topic/docs/5ekVC8iavOE;context-place=forum/docs
How to sort by day and time not a to z
I have a schedule for employees. But when we sort the sheet it puts everything by a to z the I would like it to actually put the response in the right order starting Monday morning. The problem is Friday is always first. but more importantly, 1 am is followed but 1 pm.

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



Friday, May 4, 2018

yogi_Sort Table B3:I68 and L3:S23 in 'Points Standing' By Column I And S Respectively In Descending Order

Google Spreadsheet   Post  #2443

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

question by: PhilipORoss
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/i24B3s2FF_c;context-place=mydiscussions
Sort a range that uses a =unique function
I have a spreadsheet that keeps score of a competition.

Competition is 6 rounds, and I'm using a vlookup embedded in an iferror to total the points of each rounds.
To generate the final list, I'm also using a unique function, to ensure that participants names only appear once.

Happy to post a link to a copy of the spreadsheet if someone can offer some help!

I'm trying to sort the range by points value, and the total points is calculated by =sum(range)-min(range) as the total points is total minus lowest value.
Whenever I attempt to sort the range it's repopulates into the list of names initially populated by the unique function.

I've found this by playing around with the formulas that generate the total points, with no change any time.

Anyone able to offer help?

TL;DR: Unable to sort range by points value in column G, as column A has unique function

Tuesday, May 1, 2018

yogi_Conditionally Format Cells Based on Absolute Value Difference to Another Cell

Google Spreadsheet   Post  #2442

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

question by: Chadillac
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/wgkg8WHMowI;context-place=forum/docs
Conditional Formatting Based on Absolute Value Difference to Another Cell
I'm wondering if it is possible to create custom conditional formatting of a cell based on its absolute value difference from another cell. In the spreadsheet linked below, for example, I would like to have B13 and B14 change color to indicate their difference between each other is greater than 1.

For context: these are essay scores that have been assessed twice by readers, so each "Essay ID" corresponds to the pair of scores each essay has received. I'm hoping to create custom conditional formatting that would highlight where reader scores vary by more than 1 for each column B through H. Is this possible?