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: UliToI
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