Thursday, November 23, 2017

yogi_Show Count And Average Of Values By Person In DashBoard

Google Spreadsheet   Post  #2298

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-23-2017
question by: John Paul Banaag
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/D9KuDFS3FTk;context-place=forum/docs
How to average and count the value of a specific person in a raw data?
Hello,

Thank you for your interest in viewing this topic. I'm posting this as I really need all the help I can get from you all.

I'm trying to create a sheet that has a dashboard and a raw data which would allow me to see on the dashboard the scorecard of each person by week.

I made a sample below which would let you all see what I'm trying to create. I'm only asking for the formulas that I would need to use in the green cells which you would see in my sample. Please access the link to see the sample:


I'm looking forward to all of your comments.

Thank you!





Wednesday, November 22, 2017

yogi_Conditionally Format Discounted Values That Are Below Bottom Line Price

Google Spreadsheet   Post  #2297

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-22-2017
question by: tayde
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/bPbbqge23pw;context-place=forum/docs
How do I format my sheet with colors for each cell?
I have a discount sheet I created, where each column shows me the individual price of an item when a specific discount percentage is applied. I've added a column that corresponds with the cost of an item that I don't want to go under. How can I format the cells to mark as red if a discount will go under the bottom price of an item? 
(So here I would like the 20% through the 35% of the $200 item to be marked red. I know I can do this through the conditional formatting, but is there some way to format it for the entire sheet instead of formatting each row individually?)
Bottom LinePRICED AT10%15%20%25%30%35%
$200.00$250.00$225.00$212.50$200.00$187.50$175.00$162.50
$380.00$1,100.00$990.00$935.00$880.00$825.00$770.00$715.00
Edited

yogi_Extract Row By Row Substring Using Query Function

Google Spreadsheet   Post  #2296

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-22-2017
question by: DaveGoogle
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/-GlmlC1qgaQ;context-place=topicsearchin/docs/authorid$3AAPn2wQc7O4EQsX1gkj1mKYiVqjACnXxOUJfDW9aP6REEoSb26rzIBkrBhpHOgIm3cS21f6-y3rVl%7Csort:date%7Cspell:false
How do I use substring / MID formula with Google Query?
Hi,

Below is my basic query.

=QUERY(Tracker,"select K,M,N,A,F")

This returns all the columns I need. However, I want to extract a substring of column M. Something like below:

=QUERY(Tracker,"select K,substring("M",2,7),N,A,F")

Can anyone advise the correct syntax?

Thanks

Tuesday, November 21, 2017

yogi_Compute Number Of Days Parked From In Out Data For Parking

Google Spreadsheet   Post  #2295

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-21-2017
question by: UliTol
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/nrxsBbg3BTU;context-place=forum/docs
Need help counting dates between dates
Hey,  I am a bit puzzled,  can I get some help with a formula to help count between dates.

The parking data tab shows a patron entry,   it shows the time in and time out

Say person A parked form 09/28/17 to 10/03/17
and person B parked from  10/02/17  to 10/04/17
I need to track how many people parked per day.
so it would look something like this

10/1 - 1
10/2 - 2
10/3 - 2
10/4 - 1

and so on.


https://docs.google.com/spreadsheets/d/1j9-n4VTMzGgJDN0COqRvdL-L19LCtNLk_IDbqmEt1SY/edit?usp=sharing
Thank you!


yogi_Custom Number Format Cells With k M B T And Vice Versa

Google Spreadsheet   Post  #2294

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-21-2017
question by: SharkSheaker
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/0ehdquTrIlg;context-place=forum/docs
Transforming any number typed into a cell into a k,M,B,T... shortened
There are cells, for example cell A1, where a very high number can be typed into. now the number inside that cell should be formatted to k,M,B...

for example 25.300.000 would usually turn into 2,53E+7 but i want to have 25,3 M
when numbers have to many non zeros, the value is rounded up. for example 123.475.345.876 turns into 123,5 T

because:
k stands for kilo
M for million
B for billion
T for trillion
Q for quadrillion
Qi for quintillion
etc.

thank you for any help

Saturday, November 18, 2017

yogi_Pull Row By Row Attributes Per Specification By Last Name And First Name

Google Spreadsheet   Post  #2293

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-18-2017
question by: AJ Tabuena
https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!topic/docs/joJbVBsUJIM;context-place=forum/docs
Why outside of my league.
This is a google form that I am trying to organize.
 All form responses are on the 'Form Responses 1'sheet. 

On 'Sheet2' I've been trying to generate a list of names (C2) with every Title (A) that they chose "This is me" for.

On I have manually inputted what the outcome should be. But I am have not clue where to start.
Any help would be greatly appreciated. Thank you.




Thursday, November 16, 2017

yogi_Rearrange Tabular Data CrossTab Lead Name And Rating By Category

Google Spreadsheet   Post  #2292

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-16-2017
question by: AJ Tabuena
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/XP_Hv6kGPT8;context-place=forum/docs
Match values in cells in column A, B, C and output C in another cell
Hoping someone could help with this, been racking my head trying to figure it out. This is what I have:
| Header | Column A | Column B | Column C | 
| Row 1  |    ABC   |    AAA   |     4    |
| Row 2  |    ABC   |    BBB   |     3    |
| Row 3  |    ABC   |    CCC   |     4    |
| Row 4  |    DEF   |    AAA   |     2    |
| Row 5  |    DEF   |    BBB   |     3    |
| Row 6  |    DEF   |    CCC   |     4    |

What I'm looking to accomplish is that when A and B match, it outputs C so that I can create a new table that looks like this:
| Header | AAA | BBB | CCC | 
|  ABC   |  4  |  3  |  4  |
|  DEF   |  2  |  3  |  4  |

So essentially, I would need formulas so that I can automate the numbers under the headers AAA, BBB, CCC. I really hope someone can help as I've been looking for a way to do this using INDEX and MATCH but nothing seems to work right.

Here's an example sheet of the sample and my desired result.


yogi_Compute Sum Of Amounts From Intersection Of Ranges

Google Spreadsheet   Post  #2291

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-16-2017
question by: Pierre Lbchr
https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!msg/docs/pIubguc22sM/DgE4u5-DCgAJ;context-place=forum/docs

Add the intersection of two ranges ?

've a range named for each month ("June" : 2:4, "July" :6:7 & "August" : 9:11) and a range named for the price ("Price" : D:D). Is there an operator to add the values of the intersection of the range "July" and the range "Price" to obtain the total for July ?

Thank you in advance.

Pierre

Wednesday, November 15, 2017

yogi_Compute Fee For Services Based on Multiple Parameters

Google Spreadsheet   Post  #2290

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-15-2017
question by: mattynew
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/dP57xuWxsSI;context-place=topicsearchin/docs/authorid$3AAPn2wQc7O4EQsX1gkj1mKYiVqjACnXxOUJfDW9aP6REEoSb26rzIBkrBhpHOgIm3cS21f6-y3rVl%7Csort:date%7Cspell:false
I don't know if the formula I am looking for exists...
Good afternoon everyone,

I hope you are all having a good day. I apologise in advance if I appear to becoming across lazy - while some of that is true, I have tried over and over but can't seem to get my head around the formula I should be using. I thought maybe 'lookup', vlookup etc might be right but I can't figure out the actual formula.


Above is the link to the sheet I am currently working on. (its view only but happy to adjust it to anyone who thinks they can help).

Heres what I currently have formulated: (by tab)

Input data: This is where I want to be able to punch numbers in and have the desired results pop up so no one has to leave this tab to find the answers they are looking for. 

Where I need help is the 'Fincas cleaning earnings', 'cleaning fees' and 'owners earnings' (currently no formula put in these cells)

Basically, when a number is put into the 'number of guests' cell and 'number of nights' cell, I would like a formula to grab the information from the following cells:

Fincas cleaning earnings: When a number is input into 'number of guests' and 'number of nights', is there a formula that can grab the information from the tab 'cleaning fees'. For example, if 2 people stayed 4 nights then the figure should equal: small - $80 for small / $130 for medium / $180 for large

Owners earnings: When a number is input into 'number of guests' and 'number of nights', is there a formula that can grab the information from the tab 'Net Rental minus F.P.P.H minus Cleaning fees'. For example, if 2 people stayed 4 nights then the figure should equal: small - $480 for small / $430 for medium / $380 for large

In theory it should be possible but I've totally lost it trying to figure it out. Any help will hugely appreciated. I live in the jungle so only have internet access when at basecamp, apologies in advance for any delayed response. 

Thank you so much

Matt


yogi_Count Names In B2:Z For Last Specified Number Of Occupied Columns In Row 1

Google Spreadsheet   Post  #2289

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-15-2017
question by: Tobiah81
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/xnySvk7_bto;context-place=forum/docs
A auto changing range in formula
Hey,

I'm trying to create a COUNTIF with a changing range.
I have a sheet with in theory infinite columns and 5 rows. A1 is empty. 
So the data starts from B1:B5.



Now i want to have a =COUNTIF(RANGE,"Marc") where the range is changing based on the last column used.
So right now the range would be B1:J5 (because column J is the last used).
But if i enter any data in column K the formula would have to change to B1:K5

How can i determine the last column that has any value in it?
I'm using >> =INDEX(1:1, MATCH(99^99,1:1, 1)) << but i get the value returned, not the column name

Some searching on google gave me this >> =CHAR(65+MATCH(MAX(B1:1),B1:1,0)) << but this means at column AA this wont work anymore.

On top of that, this formula should be used in the COUNTIF formula
For example => =COUNTIF(B1:CHAR(65+MATCH(MAX(B1:1),B1:1,0))12;"Marc")

Part 2 of the changing range is that "B1" isn't the start. It should be "the last column used -5"
So in this example it should be Column "J-5" => Column F.

Is this even possible?
Hope this makes any sense


Tuesday, November 14, 2017

yogi_CrossTab Items By Location Using the Latest Data

Google Spreadsheet   Post  #2288

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-14-2017
question by: HomeWarrior
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/xnySvk7_bto;context-place=forum/docs
Help creating an inventory/search tool for cellphone units
I created a form that dumps all the data into a sheet. I want to create a second sheet that will display the most current entries so that each store knows what each store has in units. 
Here is my data sheet and an example sheet to illustrate what I need.
If anyone can help me that would be great.


yogi_Compute Row By Row Running Balance Based On Debits Credits And Opening Balance

Google Spreadsheet   Post  #2287

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-14-2017
question by: Kandi B
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/o1v6524T0Xk;context-place=forum/docs
I need to have a function that will work for the whole column
I have values in F, G, and H. I'm looking for a function that will be in the current row H. It will take the value H from the previous row, and subtract F from the current row, and add G from the current row.
But each row will have different values in each row. 

Is this possible without having to make an individual function for each row?

Thanks!

yogi_Pull Employee Review Dates By Specified Year

Google Spreadsheet   Post  #2286

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-14-2017
question by: Ace Avant
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/eRM1ysbVrSs;context-place=topicsearchin/docs/authorid$3AAPn2wQc7O4EQsX1gkj1mKYiVqjACnXxOUJfDW9aP6REEoSb26rzIBkrBhpHOgIm3cS21f6-y3rVl%7Csort:date%7Cspell:false

Query by year?


Hello everyone,

I have been searching through the forums in search for a formula that would query items that are for a specific years with no luck. Maybe you all can give me any advice. What I am trying to achieve is having a master sheet named, Master Sheet and create other sheets labeled, 2016 Reviews, 2017 Reviews, 2018 Reviews, etc.. In the Master Sheet, Columns A-E contain employee information like Employee #, Name, Pay, Group, etc.. And in columns F, H, J, etc.., contain dates of evaluation performed every 6 months. How can I query information from the Master Sheet to the other sheets based on the year when a column may have 2 or 3 different years when I'm only looking for sheet 'Review 201X' each to display dates for that respective year.

Any advice is appreciated,

Thanks