Sunday, August 20, 2017

yogi_Compute Row By Row Running Total Of Value By Type Where Date is Less Than Or Equal To Date In Current Row

Google Spreadsheet   Post  #2229

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-20-2017
question by: Henrique Magalhaes
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/1cjhkWHcKC4;context-place=forum/docs

Array formula with sumif with less than condition

Hi, I have this sheet with the following columns of data:

Column A - Date: this is the date of a transaction.
Column B - Type: this is the type of the item
Column C- Value: this is the value of the transaction

For each row, I need to determine 2 values:

Column D. the sum of all values of the current type, where date is equal the current date. I've used the following formula to achieve that: sumifs(C:C;A:A;A2;B:B;B2).

Column E. the sum of all values of the current type, where date is less than or equal the current date. I've used the following formula to achieve that: sumifs(C:C;A:A;"<="&A2;B:B;B2).

However this sheet will be used with a great amount of data, and I need it to auto populate new rows with these formulas. Arrayformula does that well for the first one, and I changed it to: =arrayformula(if(row(A:A)=1;"sum of type in current date";sumif(A:A&B:B;A:A&B:B;C:C))).  Sumifs didn't work so I changed it to a formula with sumif and & operators.

However I could not think how to make it for the column E formula. Is there any way to use arrayformula with sumif (or sumifs) and "<=" condition? If not, is there any other way to auto populate the formula to new lines?

Thanks



yogi_Compute Running Total Of Value By Type Where Date is Less Than Or Equal To Today's Date

Google Spreadsheet   Post  #2228

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-20-2017
question by: Henrique Magalhaes
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/1cjhkWHcKC4;context-place=forum/docs

Array formula with sumif with less than condition

Hi, I have this sheet with the following columns of data:

Column A - Date: this is the date of a transaction.
Column B - Type: this is the type of the item
Column C- Value: this is the value of the transaction

For each row, I need to determine 2 values:

Column D. the sum of all values of the current type, where date is equal the current date. I've used the following formula to achieve that: sumifs(C:C;A:A;A2;B:B;B2).


Column E. the sum of all values of the current type, where date is less than or equal the current date. I've used the following formula to achieve that: sumifs(C:C;A:A;"<="&A2;B:B;B2).

However this sheet will be used with a great amount of data, and I need it to auto populate new rows with these formulas. Arrayformula does that well for the first one, and I changed it to: =arrayformula(if(row(A:A)=1;"sum of type in current date";sumif(A:A&B:B;A:A&B:B;C:C))).  Sumifs didn't work so I changed it to a formula with sumif and & operators.

However I could not think how to make it for the column E formula. Is there any way to use arrayformula with sumif (or sumifs) and "<=" condition? If not, is there any other way to auto populate the formula to new lines?

Thanks


Saturday, August 19, 2017

yogi_Add a Specified Number Every Nth Day From A Specified Date

Google Spreadsheet   Post  #2227

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-19-2017
question by: MathMan123
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/yK3diow3dHg;context-place=forum/docs
How to add a certain number every 30th day from a specific date?
If possible? I really hope someone might be able to help me out here if you have any ideas on how to do it please share it! 
Thanks in advance. 

     - Peter Hansen

Friday, August 18, 2017

yogi_Find The Lowest Priced Vendors By County

Google Spreadsheet   Post  #2226

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-18-2017
question by: Ben.V
https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!msg/docs/aiqY1HL93QY/p-KBW7kYCgAJ

Search for duplicates in Col B, compare number in Col C, highlight lowest number?

 
This sheet shows the following:
Vendors in column A
Counties in column B
Prices in column C
I often need to review lists like this to find the cheapest vendor in each county. Many counties have more than one vendor. Currently, I have to do this manually.
Here's what I would like a formula to do:
1.  Scan the list of counties in Col B.
2.  Find duplicate county names. This will show where there is more than one vendor per county.
3.  Compare the prices in column C for the duplicate counties.
4.  Highlight the lowest number.
For example:
See Rows 36 and 37.
Two vendors cover Hardeman.
One vendor costs 65. The other costs 95.
I would like the system to find this and highlight the 65.
Thank you very much in advance for any suggestions!!!
Ben

Sunday, August 13, 2017

yogi_Return X If The Row (columns A:E) Does Not Contain one Of The Specified Items

Google Spreadsheet   Post  #2225

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-13-2017
question by: Dana Sturdivant
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/-EnxHCZ-MIA;context-place=mydiscussions

Return "x" if row does not contain "dog" OR "cat"

I need to write a formula that returns an x if the adjacent row does not contain any of the specified text strings. 

Example: Specified text: "Dog" "Cat"

Row 1 does not return an x because it has the text "Dog" and/or "Cat" 
Row 2 returns an x because it does not contain the text "Dog" or "Cat" 

DogCatBirdFishTurtle
FishGerbilHamsterDuckRabbitx
I have attached a sample case with text in cells A2:E5, and with the desired result in F2:F5

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


Saturday, August 12, 2017

yogi_Compute Average Of Multiplier Values (read from Sheet2) for ROTX Entries Which Have Date in Column B = That In C3

Google Spreadsheet   Post  #2224

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-12-2017
question by: Sergio Ochoa
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/QXBCSG75IFk;context-place=mydiscussions
How do I combine these averageif and lookup functions?
I gave it my best shot and I am admitting defeat. Can someone help?

I am looking for a way to get the average values of the letters in sheet 1, column A with those that match column B with the value in C:2 (7/31). The result should be 2.4 (Average of 10,2,1,0,-1).

Sheet 2 has the purpose of holding the multiplier values so I can go to one place and change them if need be.

Thoughts on how to do this?

Sheet 1
A
B
C
1
ROTX
Date
Searching for
2
A
7/31
7/31
3
B
7/31
4
C
7/31
5
D
7/31
6
F
7/31
7
A
8/2
8
A
8/3
9
A
8/4
10
B
8/5
11
B
8/6

Sheet 2
A
B
1
ROTX
Multiplier Value
2
A
10
3
B
2
4
C
1
5
D
0
6
F
-1


Thursday, August 10, 2017

yogi_Conditionally Format Entries In E4:E -- Yellow Red or White Depending On Date Due

Google Spreadsheet   Post  #2223

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-10-2017
question by: Brittany Berridge
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/62Q07ikdCXs;context-place=mydiscussions

Conditional Formatting for due dates

 
Hello, I am setting up a spreadsheet that contains a due date and a date turned in column. I have the conditional formatting set up to highlight the upcoming due dates (7 days out) but I would like the formatting to go away if the 'date turned in' column contains data.