## Saturday, February 28, 2015

### yogi_Compute The Nth Number Of Its Respective Occurrence of An Entity In An Array Of Entities

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-28-2015
question by ksivanantha:
How to find the count (nth Number) of a repeating word
Dear Forum Members,

Please help me to find the count (nth Number) of a repeating word based on the occurrence (First entry 1, 2nds similar word, 2,3,..)

Example : Supplier type is repeating below & first stationery supplier to get the rank 1 and second stationery supplier Q to get 2.

Name of Supplier    Type of Supplier   Supplier Rank
A                              Stationery                1
B                              Grocery                   1
C                              Grocery                    2
X                              House Hold               1
P                              Grocery                    3
Q                              Stationery                 2
-------------------------------------------------------------------------------------------------------------------------

## Wednesday, February 25, 2015

### yogi_Spell Out An Amount Say Currency Such As Dollar And Cents As Would Be On A Check -- For Google New Sheets

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-20-2015
Note:
formula in this blog post is for Google New Sheets
Earlier in my following blog post I had formulation that worked in so called Google Old Sheets
 yogi_Spell Out An Amount Say Currency Such As Dollar And Cents As Would Be On A Check http://yogi--anand-consulting.blogspot.com/2012/11/yogispell-out-dollar-and-cents-as-would.html instead of trouble shooting and fixing the formula i rewrote it as presented in this blog post question by Mitrih:
how do I get a cell reflect "eight" when the formula is 4+4 instead of =8
how do I get a cell reflect "eight" when the formula is 4+4 instead of =8
---
This might be a better explanation.

The following formula =

(IF(OR(H3>1000000,H3<=0),"",IF(H3<1000,"",IF(MOD(H3,1000000)>=100000,INDEX(TRUNC(MOD(H3,1000000)/100000,0))&" Hundred","")&IF(MOD(H3,100000)>=20000," "&INDEX(TRUNC(MOD(H3,100000)/10000,0))&IF(MOD(MOD(H3,100000),10000)>=1000,"-"&INDEX(TRUNC(MOD(MOD(H3,100000),10000)/1000,0)),""),IF(MOD(H3,100000)>=1000," "&INDEX(TRUNC(MOD(H3,100000)/1000,0)),""))&" Thousand") & IF(H3<1,"Zero Dollars",IF(MOD(H3,1000)>=100," "&INDEX(TRUNC(MOD(H3,1000)/100,0))&" Hundred",""
...
Here is the the formula in excel:
=IF(OR(H3>1000000,H3<=0),"",IF(H3<1000,"",IF(MOD(H3,1000000)>=100000,INDEX(numbers,TRUNC(MOD(H3,1000000)/100000,0)+1)&" Hundred","")&IF(MOD(H3,100000)>=20000," "&INDEX(tens,TRUNC(MOD(H3,100000)/10000,0)+1)&IF(MOD(MOD(H3,100000),10000)>=1000,"-"&INDEX(numbers,TRUNC(MOD(MOD(H3,100000),10000)/1000,0)+1),""),IF(MOD(H3,100000)>=1000," "&INDEX(numbers,TRUNC(MOD(H3,100000)/1000,0)+1),""))&" Thousand") & IF(H3<1,"Zero Dollars",IF(MOD(H3,1000)>=100," "&INDEX(numbers,TRUNC(MOD(H3,1000)/100,0)+1)&" Hundred","")&IF(MOD(H3,100)>=20," "&INDEX(tens,TRUNC(MOD(H3,100)/10,0)+1)&IF(MOD(MOD(H3,100),10)>=1,"-"&INDEX(numbers,TRUNC(MOD(MOD(H3,100),10),0)+1),""),IF(MOD(H3,100)>=1," "&INDEX(numbers,TRUNC(MOD(H3,100),0)+1),""))&"") & " and " & IF(MOD(H3,1)<0.005,"NO",ROUND(MOD(H3,1)*100,0)) & "/100 -----------")

See attached file for what I am trying to achieve. When I fill the amount box with \$550.25 the amount written comes as "5 Hundred 5 and 25/100 -----------" instead of "Five Hundred and Fifty Dollars and 25/100-----------"

The attached file depicts the use of the formula in my previous post.

I hope this is more clear

Attachments (1)

Screenshot 2015-02-24 at 10.57.07 PM.png
------------------------------------

## Monday, February 23, 2015

### yogi_Consolidate Attributes of various Items In Rows To Unique Row Per Item And Attributes In Columns

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-20-2015
question from comment by Michael Roberts in the following blog post
yogi_Consolidate Attributes of various Items In Rows To Unique Row Per Item And Attributes In Columns
http://yogi--anand-consulting.blogspot.com/2015/02/yogiconsolidate-attributes-of-various.html

## Friday, February 20, 2015

### yogi_Compute Row By Row Expense By Category For Week Numbers In Columns

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-20-2015
post by  Marc Tyrrell:
Date range for expenses by type
Hi Folks,

I'm trying to set up a form which will allow for daily input of expenses and will produce a weekly summation of expenses by type.  The early version of the form is available athttps://docs.google.com/a/cavara.co/spreadsheets/d/1qj1aGuh9UzmrEd3A3jKSSNJ3Iu6yyEaYnVekiQG-_B4/edit#gid=0 .

What I want to do is have people enter their expenses using the form, which is recorded in the Form Response 1 tab, and use the Cumulative by week tab to create weekly summaries of the expense by type.

I am totally lost in trying to figure out how to do this!  If it was a database, I could, but not with sheets.  I would REALLY appreciate any suggestions anyone has!

Cheers,

Marc
---
Hi Chris,

Thanks - here you go.

Cheers,

Marc
----------------------------------------------------------------------------------------------------------------------------------------------

## Wednesday, February 18, 2015

### yogi_ Convert Row by Row Time Given In Column D In Text (Minutes and Seconds) Into Time In Numeric (hh colon mm colon ss)

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-18-2015
post by  Ashlee Wall:
How to format a cell from 1 minute 30 seconds to 1:30
Please help!!! I am just starting to use Google sheets with a new job. They want me to transfer data from old sheet to new sheet. The old sheet is using 1 minute 30 seconds and they want new sheet to be formatted to say 1:30 instead. Thanks in advance for your help!!
----------------------------------------------------------------------------------------------------------------------------------------

### yogi_Pull From Data Brought In By Using ImportRange Those Rows That Have In Column F One Of The Entries In Cells A1 To B3

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-18-2015
post by  Omar Kalaf:
ImportRange select everything that matches 6 of 12 possible entries in a spreadsheet
I have 1 spreadsheet that is my live data. In Col6 There are 12 possible options for cell content driven my data validation.
In a 2nd spreadsheet I am trying to pull some of that data in.
I am just wanting to pull data that matches 6 of those 12 possible cell contents AND ignore any empty cells
My 2nd sheet has the 6 criteria to match listed in cells A1 thru B3.
Trying this in cell A5 to pull the data but keep getting error.

``` =Query(ImportRange(Êº1goG0TS1_2jwlGRetREYNRVk-Q6TEy3iWG_5VXFoZlusÊº,ÊºMaster!A:FÊº),Êºselect * where Col6 ='Êº&A1&Êº' or Col6='Êº&A2&Êº' or Col6='Êº&A3&Êº' or Col6='Êº&B1&Êº'or Col6='Êº&B2&Êº' or Col6='Êº&B3&Êº' Êº,0) ```

It states unable to parse for Function Query parameter 2........

Any help would be most appreciated
--------------------------------------------------------------------------------------------------------------------------