i am trying to automatically update a formula each time a new row is added. For the columns that have a simple formula arrayformula works well, but some of the columns have a larger formula which contains larger formulas like column L2 which has the following =NOT(OR(L$1>$F2,EOMONTH(L$1,0)<$E2))*(MIN(EOMONTH(L$1,0)+1,$F2)-MAX(L$1,$E2)) the formula does not work

I have attached the sample google sheet for your reference. i woul like column L to automatically update when a new row is added

I have attached the sample google sheet for your reference. i woul like column L to automatically update when a new row is added

Hi everyone,

I'm really hoping you can help me with this. I've worked on this for some time, but it seems like I'm going in circles. What I need is rather simple, and here it is:

1) You purchase 10 shares of AAPL stock at $110 per share. You have a buying average of $110 per share (duh), with an open invested equity of $1,100 on your imaginary balance sheet.

2) You sell 5 of those 10 shares at $125 per share. You receive $625 in funds. $75 goes to P&L as income. -$550 goes back to balance sheet to offset the asset sale. You now hold 5 shares at $110 per share.

3) You purchase 5 more shares at $105 per share. You now have 10 shares at an average price of $107.50 on your balance sheet.

4) You sell all 10 shares at $135 per share. You receive $1,350 in funds. $1,075 goes to your balance sheet to offset your asset sale. The remaining $275 goes to P&L as income. Balance sheet shows $0 for that particular asset.

5) Finally, you purchase 5 more shares of the same AAPL stock at $95 per share. Your balance sheet shows (or at least should show) an average price of $95 per share, and total invested equity of $950 on the balance sheet.

Can anyone help me do this on google sheets? I'm thiiiiiiiiiiiiis close to killing myself over this.

Thanks in advance for saving my life.

]]>I'm really hoping you can help me with this. I've worked on this for some time, but it seems like I'm going in circles. What I need is rather simple, and here it is:

1) You purchase 10 shares of AAPL stock at $110 per share. You have a buying average of $110 per share (duh), with an open invested equity of $1,100 on your imaginary balance sheet.

2) You sell 5 of those 10 shares at $125 per share. You receive $625 in funds. $75 goes to P&L as income. -$550 goes back to balance sheet to offset the asset sale. You now hold 5 shares at $110 per share.

3) You purchase 5 more shares at $105 per share. You now have 10 shares at an average price of $107.50 on your balance sheet.

4) You sell all 10 shares at $135 per share. You receive $1,350 in funds. $1,075 goes to your balance sheet to offset your asset sale. The remaining $275 goes to P&L as income. Balance sheet shows $0 for that particular asset.

5) Finally, you purchase 5 more shares of the same AAPL stock at $95 per share. Your balance sheet shows (or at least should show) an average price of $95 per share, and total invested equity of $950 on the balance sheet.

Can anyone help me do this on google sheets? I'm thiiiiiiiiiiiiis close to killing myself over this.

Thanks in advance for saving my life.

I am generating attendance sheet in excel 19 in my computer. But when I upload it in Google Sheet then it not works for me. Fore more information please see the attachment.

Thanks in Advance.

Thanks in Advance.

Hello!

I have a Google Spreadsheet. On on sheet, I track how many grams of each food ingredient I eat over days, weeks, months, etc. I.e. 20 g. almonds, 780 g. coffee, 87 g. dark chocolate, etc.

On another sheet, I keep track of a bunch of symptoms I experience throughout the day including brain fog, headaches, etc. using 1 - 10 scales.

What I want to do is create some type of formula that will give a count of a particular food ingredient if it is correlated with a low average weekly score in some symptom.

This isn't the case, but let's say hypothetically I was allergic to peanuts and had a mildly worse headache on the weeks that I ate it. I would want Google Sheets to find that correlation and flag that food as being suspect and give me a count of how many weeks peanuts were associated with a worse than average subjective headache rating.

Hopefully that makes sense, I'm still a newbie when it comes to coding so I could use some help!

Aaron

]]>I have a Google Spreadsheet. On on sheet, I track how many grams of each food ingredient I eat over days, weeks, months, etc. I.e. 20 g. almonds, 780 g. coffee, 87 g. dark chocolate, etc.

On another sheet, I keep track of a bunch of symptoms I experience throughout the day including brain fog, headaches, etc. using 1 - 10 scales.

What I want to do is create some type of formula that will give a count of a particular food ingredient if it is correlated with a low average weekly score in some symptom.

This isn't the case, but let's say hypothetically I was allergic to peanuts and had a mildly worse headache on the weeks that I ate it. I would want Google Sheets to find that correlation and flag that food as being suspect and give me a count of how many weeks peanuts were associated with a worse than average subjective headache rating.

Hopefully that makes sense, I'm still a newbie when it comes to coding so I could use some help!

Aaron

I can't see any way to query data other than Database or text and html.

I need to query various xls files...I can't figure out how to add them on Mac Excel O365. There doesn't even seem to be an option for querying other tables in the same workbook...what am I missing?

]]>I need to query various xls files...I can't figure out how to add them on Mac Excel O365. There doesn't even seem to be an option for querying other tables in the same workbook...what am I missing?

Hi Excel Forum, Thanks for your help in the past with my projects. I just had a quick question. It seems simple but can't figure it out.

Our goal is to perform a search on Column A, "Bad List", on sheet Bad List Reference and if any word (e.g., "student") is contained in Column F, "Title", then that entire row gets deleted.

For example, since Julian Rutz, row 5, has student in his title, and the word student exists in Bad List, his entire row would be deleted.

Ideally, we want a Google Script to run every week to check the list against Column F, for title, and delete the bad rows.

Here is the GSheet: https://docs.google.com/spreadsheets...6k8rsiL2Pr5_TQ

Will you please advise?

Thanks again, I really appreciate it.

Alex

]]>Our goal is to perform a search on Column A, "Bad List", on sheet Bad List Reference and if any word (e.g., "student") is contained in Column F, "Title", then that entire row gets deleted.

For example, since Julian Rutz, row 5, has student in his title, and the word student exists in Bad List, his entire row would be deleted.

Ideally, we want a Google Script to run every week to check the list against Column F, for title, and delete the bad rows.

Here is the GSheet: https://docs.google.com/spreadsheets...6k8rsiL2Pr5_TQ

Will you please advise?

Thanks again, I really appreciate it.

Alex

I have Google Spreadsheet Document with a **Master** Sheet. Rows in this Master sheet will keep on increasing.

Each row in the Master Sheet has some numeric values (from**column F to M**) which need to be splitted into smaller lots using the corresponding values in **columns P to W**.

There is a custom menu (**Split Row Value**) which is supposed to function as below

Create a new sheet Nnamed "**SPLIT-n**", where n is a sequential number starting with 1. Copy the entire heading row from MASTER to SPLIT-n on row 1.

Copy the first chosen row (Column N) where SPLITTED Column (column X) is Blank

Paste the entire row from MASTER in Newely created SPLIT-n sheet on Row 2.

Split the values in Column F to M by dividing them corresponding value in Column P to W and paste the results below.

Repeat step 4 till there are no values left in column F to M.

Fill "SPLITTED" in Master Sheet on the same row which was splitted into smaller lots and apply the same formatting.

Copy the next chosen row from Master and paste on next available row in SPLIT-n

Repeat Step 4-7 till all chosen rows are splited.

I need the code for the UDF which will perform the above mentioned task. I have created the first SPLIT Sheet manually for reference.

Please help me with UDF Code. Thanks

Link below to my document with sample data.

https://docs.google.com/spreadsheets...4xP6dt/pubhtml

]]>Each row in the Master Sheet has some numeric values (from

There is a custom menu (

Create a new sheet Nnamed "

Copy the first chosen row (Column N) where SPLITTED Column (column X) is Blank

Paste the entire row from MASTER in Newely created SPLIT-n sheet on Row 2.

Split the values in Column F to M by dividing them corresponding value in Column P to W and paste the results below.

Repeat step 4 till there are no values left in column F to M.

Fill "SPLITTED" in Master Sheet on the same row which was splitted into smaller lots and apply the same formatting.

Copy the next chosen row from Master and paste on next available row in SPLIT-n

Repeat Step 4-7 till all chosen rows are splited.

I need the code for the UDF which will perform the above mentioned task. I have created the first SPLIT Sheet manually for reference.

Please help me with UDF Code. Thanks

Link below to my document with sample data.

https://docs.google.com/spreadsheets...4xP6dt/pubhtml

Hi, again!

I'm not sure if my post earlier was posted or not. So pls pardon should there be a double post.

I hope somebody can help with this simple problem.

I have a table of metric type, target, actual.

Name Metric Type Target Actual

EmpA Positive 45% 0%

Negative 70% 100%

Positive 17% 37.91%

EmpB Positive 45% 55%

Negative 70% 0%

Positive 17% 90%

For each metric type, I need to calculate the Gap to Goal and the % Achievement of the employee.

*Negative = the lower the actual score is, the better.

Also, the data will be presented using pivottable. Should the Gap to Goal and %Achievement be shown as Sum or Average?

Pardon if the sample data is not in excel form, I am using a mobile phone.

Thank you in advance.

]]>I'm not sure if my post earlier was posted or not. So pls pardon should there be a double post.

I hope somebody can help with this simple problem.

I have a table of metric type, target, actual.

Name Metric Type Target Actual

EmpA Positive 45% 0%

Negative 70% 100%

Positive 17% 37.91%

EmpB Positive 45% 55%

Negative 70% 0%

Positive 17% 90%

For each metric type, I need to calculate the Gap to Goal and the % Achievement of the employee.

*Negative = the lower the actual score is, the better.

Also, the data will be presented using pivottable. Should the Gap to Goal and %Achievement be shown as Sum or Average?

Pardon if the sample data is not in excel form, I am using a mobile phone.

Thank you in advance.

I had this script set up in an excel sheet that quickly jumped to the corresponding record in another table when double clicking on a cell with an index formula that pulled the results from the said table (thanks to help from this site). It worked brilliantly. However, it does not work in the cloud or on tablets, like anything VBA. So I have turned to Google sheets to see if I can replicate the file. I am currently stuck on this VBA script. Is there anyone here who is proficient with Google Sheets that can help me turn this VBA into a GAS scripts if at all possible. Or know of any alternatives. I've asked on Google forums but not having much luck.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim Parm As String, _

FoundRow As Long

Parm = Target.Value

With Sheets("Jobs Database")

.Select

.Range("A1").Select

.Cells.Find( _

What:=Parm, _

After:=ActiveCell, _

LookIn:=xlValues, _

LookAt:=xlPart, _

SearchOrder:=xlByRows, _

SearchDirection:=xlNext, _

MatchCase:=False, _

SearchFormat:=False).Activate

FoundRow = ActiveCell.Row

ActiveCell.EntireRow.Select

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _

"=ROW()=" & FoundRow

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorAccent5

.TintAndShade = 0.599963377788629

End With

End With

End Sub

Thank you :)

]]>Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim Parm As String, _

FoundRow As Long

Parm = Target.Value

With Sheets("Jobs Database")

.Select

.Range("A1").Select

.Cells.Find( _

What:=Parm, _

After:=ActiveCell, _

LookIn:=xlValues, _

LookAt:=xlPart, _

SearchOrder:=xlByRows, _

SearchDirection:=xlNext, _

MatchCase:=False, _

SearchFormat:=False).Activate

FoundRow = ActiveCell.Row

ActiveCell.EntireRow.Select

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _

"=ROW()=" & FoundRow

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorAccent5

.TintAndShade = 0.599963377788629

End With

End With

End Sub

Thank you :)

I don't know how to fit the problem in the title.

But am trying to create a formula which

checks the cell text whether it matches with the text in another cell of another column

if matched then the data of that row would be multiplied by the data given in different cell.

Am using Google Excel for this.

M14 is the cell where am trying to put the formula

K14 is the text which is to be matched in the cell range of C3:C12

When matched then the cell of that row under the column E took as the first value for the product

and L14 is the second value to be taken.

Am not sure of how to make it such that it works.

But am trying to create a formula which

checks the cell text whether it matches with the text in another cell of another column

if matched then the data of that row would be multiplied by the data given in different cell.

Am using Google Excel for this.

M14 is the cell where am trying to put the formula

K14 is the text which is to be matched in the cell range of C3:C12

When matched then the cell of that row under the column E took as the first value for the product

and L14 is the second value to be taken.

Am not sure of how to make it such that it works.

Im looking to set up a golf league on google sheets.

We have 250 members and play 12 tournaments,

I want to set up a spreedsheet that updates league positions when a tournament has finished so points can be awarded to each member.

My problem is i havent got a clue how to do this as im new to all this,

John

]]>We have 250 members and play 12 tournaments,

I want to set up a spreedsheet that updates league positions when a tournament has finished so points can be awarded to each member.

My problem is i havent got a clue how to do this as im new to all this,

John

hello all,

Can someone pls see why i HAVE A Value error on my tab: Fixtures/Predictions, celles I5:L5

I believe the error is linked to cell: H7 WHICH is dependent upon the tab called whatsapp.

i AM inputting a date in the whatsapp tab cell AE2 then I get the problem (value error)

Any advice much appreciated.

sheet: https://docs.google.com/spreadsheets...it?usp=sharing

]]>Can someone pls see why i HAVE A Value error on my tab: Fixtures/Predictions, celles I5:L5

I believe the error is linked to cell: H7 WHICH is dependent upon the tab called whatsapp.

i AM inputting a date in the whatsapp tab cell AE2 then I get the problem (value error)

Any advice much appreciated.

sheet: https://docs.google.com/spreadsheets...it?usp=sharing

I have a large financial model I am building with over 100 named cells and ranges.

As I have building the model, I notice that I want to keep like kind cells named with the same prefix and etc.

There is no name manager in Microsoft Office 365 Excel for Mac.

To create an organized and easily understood list of named cells and ranges I have found that I am in need to changing many of the names already defined.

Is there a way to change the name and the change promulgate through the workbook so I dont have to find every use of the old name and change it to the new name?

]]>As I have building the model, I notice that I want to keep like kind cells named with the same prefix and etc.

There is no name manager in Microsoft Office 365 Excel for Mac.

To create an organized and easily understood list of named cells and ranges I have found that I am in need to changing many of the names already defined.

Is there a way to change the name and the change promulgate through the workbook so I dont have to find every use of the old name and change it to the new name?

Trying to insert the following formula: =vlookup(C2,IMPORTRANGE("LINK","AA!c1:c2978"),6,0) to lookup between to different sheets

but it keeps telling me** VLOOKUP evaluates to an out of bounds range.**

Both files are attached plz help

]]>but it keeps telling me

Both files are attached plz help

I have a financial model I am building in excel. There are multiple unit types and each unit type has a monthly absorption rate.

I have attached the workbook.

The columns I am referencing are on the Project Assumptions sheet, columns Q, R & W

Column Q - Monthly Absorption

Column R - # of Units by Unit Type

Column W - Monthly Unit Rent

I am trying to figure how to write an efficient formula that will calc the rent based absorption rates and sum the unit types. The monthly absorption is the number of units that lease up on a monthly basis. Rental income is calculated by multiplying the monthly rent by the monthly absorption plus the previous months rental income.

The formula I have written is "=G19+IF(H2*'Project Assumptions'!$Q4>='Project Assumptions'!$R4,0,'Project Assumptions'!$Q4*'Project Assumptions'!$W4)" and this works for 1 unit type. I would have to write this same formula 48 times to account for every unit type. The formula is on row 19 on The Operating Forecast tab.

Is there a better way?

I have attached the workbook.

The columns I am referencing are on the Project Assumptions sheet, columns Q, R & W

Column Q - Monthly Absorption

Column R - # of Units by Unit Type

Column W - Monthly Unit Rent

I am trying to figure how to write an efficient formula that will calc the rent based absorption rates and sum the unit types. The monthly absorption is the number of units that lease up on a monthly basis. Rental income is calculated by multiplying the monthly rent by the monthly absorption plus the previous months rental income.

The formula I have written is "=G19+IF(H2*'Project Assumptions'!$Q4>='Project Assumptions'!$R4,0,'Project Assumptions'!$Q4*'Project Assumptions'!$W4)" and this works for 1 unit type. I would have to write this same formula 48 times to account for every unit type. The formula is on row 19 on The Operating Forecast tab.

Is there a better way?