yogi_Compute Unit Rate For Specified Quantity Of Items Based On Table Of Multiple Quantity_Unit Rate Table

                                           Google Spreadsheet   Post  #1950
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr-28, 2015
question by Kiwi67mum:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/0CAYgbVvC1c
Multiple formulas.

I have 3 columns, each with different amounts.

I enter the quantity in Col B and would like it to identify what the $ amount is that is equal to the quantity. I don't think it is possible but it can't hurt to ask.

          

                                                                                                                Quantity            Rate               1-5 units              6-25 units     Over 25 units

Stencil

Unit

$22.50

$18.50

$15.00

-------------------------------------------------------------------------------------------------

yogi_Compute Bonus For Members Of Teams On Specified Week By Week Basis

                                           Google Spreadsheet   Post  #1949
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr-24, 2015
question by Jeff Underhill:

https://productforums.google.com/forum/#!mydiscussions/docs/0X8eG6WRUvY
Countif sum in date range between two sheets

Background: I'm a teacher gamifying my classroom and helping students learn to be prepared. Your help is appreciated by me and my 15 fifth graders!!

Copy of the sheet: https://docs.google.com/spreadsheets/d/1MG5QZfBCos1UzSUE9CQtpHjGNstLxxa53p4JMiRzjpk/edit?usp=sharing

Formula Needed: I'm attempting to count variables p, l, and u (p = prepared; l = late; u = unprepared) in sheet Q4 by weeks on sheet Weekly Bonus XP. I need the formula to be on sheet Weekly Bonus XP in each cell, beginning with cell H2 through V12. I need the new formula to use this formula from sheet Q4

=COUNTIF(C4:C, "p")*5+COUNTIF(C4:C, "e")*5+COUNTIF(C4:C, "l")*3+COUNTIF(C4:C, "u")*1

in an array formula, summing the above variables based on dates. The formula would use the date from sheet Q4 in column A and match it to the range between Monday and Friday on sheet Weekly Bonus XP in rows C through G.

For example, for each p, l, or u David earns in column C between March 31 and April 3 on sheet Q4, the formula on sheet Weekly Bonus XP would sum those values in the week March 31 - April 3 in cell H2.

I know precisely what I'm trying to accomplish and have tried many variations, to no avail. I am studying the syntax, and your help would be very appreciated!!

---

Hi Yogi,

I'm attempting to count the p, l, and u variables in sheet Q4 based on dates and sum them in a cell, per week and student, on sheet Weekly Bonus XP. For each student name in Q4, I need to count up the p, l, and u variables in that student's column IF those variables fall in a date range.

I need a formula on sheet Weekly Bonus XP in cell H2.

I need that formula to look something like =arrayformula(COUNTIF(C4:C, "p")*5+COUNTIF(C4:C, "e")*5+COUNTIF(C4:C, "l")*3+COUNTIF(C4:C, "u")*1)sum(date...))

p = 5 points

e = 5 points

l = 3 points

u = 1 point

So sheet Q4 column C variables will be summed for the week for the student on Sheet Weekly Bonus XP cell H2. That is, IF the date in sheet Q4 column C matches the range of dates in sheet Weekly Bonus XP between columns C2 and G2. The only dates needed in the formula would be C2 and G2, I imagine.

I would like the formula to be reiterative for each student and week.

Does this help? Appreciate your reply!

---------------------------------------------------------------------------------------------------------------------------------------

yogi_LookUp Scores For Specified Names If There Are Matching Scores For Corresponding Names In Sheet1 And Sheet2

                                           Google Spreadsheet   Post  #1948
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr-22, 2015
question by Ms. Cleveland:

https://productforums.google.com/forum/#!mydiscussions/docs/xk0vc9YTJyE

Compare columns in different Google Spreadsheets

I have 2 separate spreadsheets that I want to compare. First, I want to compare names in 2 columns in different spreadsheets/tabs. Second, it their is a match, compare the numbers in 2 columns in different spreasheets/tabs that are associated with the name match. Last, if the names and numbers match, place the number next to the name in another column, in the second spreadsheet. The names in the last column are already populated. Here is what I have so far. 

=IF(AND($D$2:$D$100=QBW2!$O$2:$O$100,$E$2:$E$100=QBW2!$P$2:$P$100,E2:E100))

I want to match a name in column D(sheet1) to column O(sheet2).

 I want to match a number in column E(sheet1) to column P(sheet2).

 If the information matches, place the number next to the name(which is in D) in column P(sheet2).

---

Here is the link below.

https://docs.google.com/spreadsheets/d/16SkjPTta11EzpvLqIwGd4MmyMC3Bb6QNSb2Trk7hHG8/edit?usp=sharing

My Objective:

If sheet1 column B matches with sheet2 column C 

AND

If sheet1 column C matches with sheet2 column D

THEN

Place their matching score next to their name in column I

IF NO MATCH OCCURED

Place a "0" in the column next to their name

The names are already populated in row H in order.

------------------------------------------------------------------------------------------------------------------

Parent View List

yogi_Rearrange Original Table Of Item Category Size Into New Table Of Item Size Category And Quantity

                                           Google Spreadsheet   Post  #1947
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr-14, 2015
question by Brian Gorrasi:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!category-topic/docs/spreadsheets/fgZs-JH929A
Count an entry once
Hi All,

I am trying to create a formula for a table but cant figure out how to get it going.  I have a table that has the 3 headers listed below:

Item
Category 
Size

Within the category section are 3 possible terms (High, Mid, Low).

Within the size section are 3 possible terms (Large, Medium, Small).

Within the Item section are multiple terms (and constantly changing)

My goal is to create a second table from this original table that gives me a numeric count of the total items based of their category and size.

Ex.
Original Table
Item      Category     Size
hat            High         L
Hat           high          L
Shoe         High         L
Hat            Mid          M
Shoe         High          S

New Table (from info collected from table 1)

Item           Category      Size        Quantity
Hat               HIgh            L                2
Shoe             HIgh            L                1
Hat               Mid             M                1
Shoe             HIgh            S                1

What formula would I need to write in the Quantity column to give me the proper count per item?
----------------------------------------------------------------------------------------------------------------------------

yogi_Conditionally Format Cells In B5:B If Date In B5:B Is Before Template Update Date In Cell B3

                                           Google Spreadsheet   Post  #1946
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr-14, 2015
question by Speech Scamaticus:

https://productforums.google.com/forum/utm_medium=email&utm_source=ba_notification#!category-topic/docs/spreadsheets/oGSAqVChq84

Conditional Format based on date less than a calculated date name range

I have in $K$5 cell a calculations for a date 2 1/2 years before the current date which does give me the date I want.
 

=today()-912

On a test spreadsheet, I have created a range for $B$3 to $B$3 called TemplateUpdate as this is the date I want to know if any of my clients last template update is before this date.  FYI, I do biennial website redesigns on all clients and need to know if one is past due to be updated. :)

So I created a conditional for a cell that contains the last date I updated a template for a client.  It format cells with a color if 'Date is before' 'exact date...' 'TemplateUpdate'.  All of the cells in the range B5:9 are formatted with the color yet only 1 of those cells, $B$5 has a date that ie before TemplateUpdate.

Here is a link to the shared spreadsheet if anyone can tell me how to get this so that only cell $B$5 has the 'mauve' background color.

https://docs.google.com/a/webbering.com/spreadsheets/d/1EiTyfMDyH5PnQXUhAuHmLfBQK4nBKWiyI_H0iFol1Go/edit?usp=sharing

Thanks.

Luke

yogi_Conditionally Format Cells Of A Column Based On Cells Of Column Housing Specified Set Of Phrases

                                           Google Spreadsheet   Post  #1945
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr-14, 2015
question by Speech maven:
https://productforums.google.com/forum/utm_medium=email&utm_source=ba_notification#!category-topic/docs/spreadsheets/_GzEorA7OMM
Multiple exact matches in conditional formatting
I'm trying to use conditional formatting to give a distinct background color to groups of words.  E.g. the words: Horse, Cow, Hog will have a blue background and the words Tower, House, Tent a red background.  Is there a way to enter these as two rules rather than 6? 
----------------------------------------------------------------------------------------------

yogi_Split An Array Of Phrases With A Specified String As A Delimiter

                                           Google Spreadsheet   Post  #1944
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr-14, 2015
question by JGM013:

Splitting text and re-joining it in Sheets
I am sure there is a straightforward way to do this. Essentially I am trying to split a text cell and rejoin portions of it on a condition.

The text cell to split will have two MLB team names with "at" in between them. I want to separate the team names into two separate cells getting rid of the "at". 

I have a test sheet showing what I am trying to accomplish. 

https://docs.google.com/spreadsheets/d/12z2feKtlRPjN4ROMSj1LyeiDCjaqYFa_uFvCfk2ZlnQ/edit?usp=sharing

The data I have to split into two team names is in Column A. Using a simple Split command with a space as a delimiter populates Columns E thru K. What ideally I would like to get through this process for expect results is M1 & N1.

I am not sure if I can search an array, find the "at", separate and join everything both left and right of the "at" into one cell containing the full name. I am thinking I might need some sort of a Regex expression to accomplish this and that is beyond my knowledge of Sheets.

Thanks to anyone who has time to look at this.

-------------------------------------------------------------------------------------------------------------------------

yogi_Compute Pay For Hours Worked At PayRates As Specified For WeekDay Work And WeekEnd Work

yogi_Compute Row By Row Score 0 for 'a' 1 for 'b' 2 for 'c' And Total Score In Header Row

                                           Google Spreadsheet   Post  #1942
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr-11, 2015
question by mos Bas:

https://productforums.google.com/forum/utm_medium=email&utm_source=ba_notification#!category-topic/docs/spreadsheets/xGmQTxAvEeE

i want to calibrate answers

I am a psychiatrist and I am building a questionnaire which is a MCQ style, in which the responder will choose one of 4 answers, and each choice will have a score

(e.g: for the same question if responder chooses number a answer he will take 0,

 if he choses number b answer he will take 1 score,

if he choose number c answer he will take 2 points and so on )

 and according to his score he will be graded to either mild moderate or sever illness,

 the problem is when I use flubaroo it does not allow me to do this, I can give point scoring to only one single choice

thanks alot

---

Dear Mr Houdini

thanks a lot for your kindness, I tried to share it with you but I couldn't because I must have your e-mail address

an example of questions is like this

1) Do you feel depressed

     a- a little           (this answer should receive score=0)

     b- average         (this answer should receive score=1)

     c- a lot               (this answer should receive score=2)

2) Do you feel fatigued

     a- a little           (this answer should receive score=0)

     b- average         (this answer should receive score=1)

     c- a lot               (this answer should receive score=2)

then we shall add the score

I am very grateful to your kind help

------------------------------------------------------------------------------------------------------------------------------

yogi_Given An Array Of Costs And An Array Of Net Profits Compute The Selling Prices To Include A Specified Tax To Be Levied On Sales

                                           Google Spreadsheet   Post  #1941
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr-10, 2015
question by Alex Staples:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/hlroyrsqFX4
How to formulate a selling price based on a desired specific profit range

My sample doc attached:

I have been trying to work on this and can't quite figure out how to get this to work right

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

If we were to be only working off of ROW 8  which has a TOTAL COST OF 201.59 (H)  I am trying to see what formula to use to in (J - N) that would tell me what I need to sell my product for to gain the profits shown in  J7 - N7

A contributer gave me the  formula "  =index(H8:H/(1-13%)+iferror(regexextract(J7:N7,"(\d+) Profit"))) " which gives me an accurate answer in column J, but kinda messes up the rest of the columns. plus i think i read that regexextract is for text not numbers.

The answer in K should be something like $237.47 because that would leave me my total cost + $5.00 after a 13% deduction L answer should be 243.21 leaving me with 10$ after fees and my cost ... etc

basically i need it to say something like [x = column J -N] then (x - 13% = (column H + "desired profit"))

i hope that makes sense.

If you were to say it in your head it would make sense.... "what minus 13% would give me a $5, $10, $25, or $50 profit over my cost" i need a formula that tells me what "what" is lol. thanks so much i know i posted this earlier but did not want it to get buried. I appreciate the contributors time and effort.... thanks.

-------------------------------------------------------------