yogi_Multi_Criteria Row By Row LookUp And Computation

Google Spreadsheet   Post  #2203

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-07-2017

question by O Ramirez:

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

Please help me figure out an IF formula for two ISSUES I am having!!!

Hi need help figuring out how to automate the Total & Fees columns every time we put the trip mileage?

***EXAMPLES FOR TOTALS

Example 1 : C2 should be $15 because it is ambulatory and it is equal or between 16 & 30 miles. 

Example 2 : C6 should be $20 because it is wheelchair and it is equal or between 16 & 30 miles.

***EXAMPLES FOR FEES(only applicable to wheelchair)

Example 1 : D3 should be $25 because it is wheelchair trip but only One Way

Example 2 : D6 should be $50 because it is wheelchair trip and Round Trip

https://docs.google.com/a/atlas-transit.com/spreadsheets/d/1Fd19KfRpvGG99Cr3wWwuyGAAhXqYSXuNquOaqyPs638/edit?usp=sharing

yogi_Show In Sheet2 Results From Combining Data In Sheet1

Google Spreadsheet   Post  #2202

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-06-2017

question by Lalla Lilley:

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

If same Name, combine data in Column B???

Here is my sample file:

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

Here is what I'm trying to do :

Sometimes on a cruise there are several tips. Customer pays BEFORE cruise (Pre-Included Tip) and then decides they want to give them more at the end of the cruise.  

The Sample file shows how the csv file looks when this is done (Sheet 1 yellow).

I need code for a Sheet 2 that finds the "like" names and combines the total into one row of data.

I left the cell blank on Sheet 2 so that others can test code. I am hoping I shared it right this time and it allows editing.

If not, please let me know.

Thanks so much!

 L ~

yogi_Compute Total Hours Worked From Daily Log Of From To Hours

Google Spreadsheet   Post  #2201

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-06-2017

question by CooperElle:

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

How to calculate hours in google sheets

There is a specific formula (or function) I'm looking for in order to add all of my hours worked each week. I don't need one to calculate each day. For example it would look like:

((FORMULA))

1 Mon:   9-5 = 8hrs

2 Tues:  9-5 = 8hrs

3 Wed:  9-5= 8hrs

4 Thur:  9-5= 8hrs

5 Fri:     9-5= 8hrs

6 Sat:    OFF

7 Sun:   OFF

Total hours worked: ??

yogi_Sort Multiple Entries By Run Time In Descending Order

Google Spreadsheet   Post  #2200

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-04-2017

question by Michelle Greenwalt:

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

Using Index & Match on columns that contain repeat entries
Hello - 

I'm creating a workbook that will help us run our swim meets more efficiently. On the first sheet the home and visiting coaches list their swimmers and their best times. The second page then sorts those times from fastest to slowest with 4 colums - the first is the swimmer's name, second their time, third is the list in ascending order from fastest to slowest (using SMALL) and then the last column matches the time in column 3 to the name in column 1 and shows the name (using INDEX & MATCH.) 

These are then pulled into a 4th sheet that creates a list for each lane for the timers to use. Currently I'm manually building the sheet, but that's another question on how to make that easier. 

The issue that I have is that some kids have duplicate times (28.8 and 28.8) and INDEX & MATCH stop at the first instance of the time and match that name to the cell. So then I have two results in the 4th column for the first swimmer who has 28.8 and zero for the second. Ideally it would show the first and then the second below it in alphabetical order if the times were the same. 

Any ideas how I can have the formula disregard the first after it has been used? Thank you!!!

yogi_Pull From Data In Another Sheet Row By Row Sum Of Entities And Total At The Bottom

Google Spreadsheet   Post  #2199

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-04-2017

question by SATH59:

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

Help on a Query

Hi

In this sample doc:

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

How can i achieve correctly the query im tryng to do ?

I have a tab named query where i explain better my Expected result for better understanding

Also need a total row below my query

Thanks !

yogi_Return Offset Cell Value Based On Maximum Value In A Data Range

Google Spreadsheet   Post  #2198

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-04-2017

question by CRODIS:

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

Returning offset cell value based on maximum value in a data range

That was the best title I could come up with, sorry :(

I'm trying to find the maximum value in a particular unsorted data range, then return the offset of a value of any equal cells.

..that's still a terrible explanation, so here's an example:

I want cell O6 to simply be, "=MAX(B7,E7,H7,K7,B17,E17,H17,K17)" but then I want cell N6 to take each Cell in that same range that returns the same value as cell O6 will display, then return a list of people that have that value in that range. So if, for example, the highest value in the unsorted range was a 12, and cells E7 and E17 both had that value, I'd want cell O6 to simply say "12," and N6 to say, "Person 2, Person 6" since those are the offset values I'd want to use to populate the list.
God I hope that makes sense.

One additional point: I want to preserve the formatting if at all possible; if it's absolutely necessary to use a sorted list, I will, but definitely prefer a solution that lets me leave it unsorted.

THANK YOU! 

yogi_Compute True Range (max of Hig-Low abs(High-Closeyest) abs(Low-Closeyest)) for a Ticker Symbol

Google Spreadsheet   Post  #2197

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-03-2017

question by Tommy Schaefer:

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

Calculating Average True Range with Google Finance

I was wondering if anyone figured out how to calculate Average True Range (ATR) using Google's finance function. I have figured out (I believe) how to calculate the True Range with this:

True Range =max((GoogleFinance(ticker,"high")-GoogleFinance(ticker,"low")), ABS(GoogleFinance($A2,"high"))-GoogleFinance(ticker,"closeyest"), ABS(GoogleFinance(ticker,"low"))-GoogleFinance(ticker,"closeyest"))

However, I'm drawing a blank on how to do the same calculation for the previous 13 days. Essentially I want to know the ATR for a ticker over 14 days without having to store the True Range for all 14 days. I'd rather the cell just do all the calculations and displayed the ATR. Can anyone help??

Thanks!