Google-sheets – Google Sheets forumla help

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryregex

With my sheet, I need it so that if the job is any type of "Repair" then it minuses 1000 from the business profit but if it's anything else it doesn't minus the 1000 from the business profit. I have spent a bit trying to work this out however I am completely unsure on how to do this.

The current formula I use is:

=IF(J4=TRUE,SUM(F4-I4)-1000,"")

which makes sure it won't appear until the paid is done and also removes the $1000 from the price however when I choose something like "Tow" or "Lock Pick" it will still remove the $1000.

A few things I have tried are

=IFS(QUERY(E4, "Where E contains 'Repair'")<>"",F4-I4-1000,E4="Tow",F4-I4)

I am rather new to all these sheets so formatting will be wrong which is why I believe the above didn't work.

=IF(E6=INDEX(Key!$D$1:$D$3,MATCH(E6,Key!$D$1:$D$3,0),0),F6-I6-1000,F6-I6)

I have another page with "Key" on it which is used to grab the names and prices for everything and matching it. I thought that with the above formula that if I can get it to match the repair types then it would output the -1000 for that else it wouldn't minus anything from it. I am not sure if the formatting is wrong however it seemed fine to me.

Photo for reference, I can explain anything needed.

Best Answer

delete everything in H4:H range

paste this into H4 cell:

=ARRAYFORMULA(IF(J4:J=TRUE, 
 IF(REGEXMATCH(LOWER(E4:E), "repair"), F4:F-I4:I-1000, F4:F-I4:I), ))

Related Solutions

Google-sheets – Google Sheets ARRAYFORMULA not copying form responses

I would suggest this if you are simply copying to a new sheet.

=query('Form Responses'!A:G, "SELECT * ")

Syntax QUERY(data, query, [headers])

The 'Form Responses'!A:G, is the data range of the source sheet that you would like to draw data from.

The "Select * ", is the query and will select everything within the range defined previously in the same order.

I did not know this when I first started using Query so I will share. If you want to select specific columns but reorder them plus leave other columns out, you can do this by getting specific with your SELECT

ie. You have Data in Columns A:F but you want to leave D and F out and put E before C in your new sheet, You can use this "SELECT A,B,E,C"

You can read more about it here its quite powerful.

You can format the new sheet to your hearts delight and it will always update when you enter new data. As long as you have data in the range you've defined in the query.

Google Sheets – Select Earliest or Latest Entries of a Subset

While filter is intuitive, query is a more powerful tool, and is better suited for your task.

Suppose A has the timestamp, B the type of product, and C the data you want to average. The command

=query(A:C, "select C where B = 'ProductName' order by A desc limit 10")

selects 10 most recent transactions for the given product name. If you want to average these, put the average around the command:

=average(query(A:C, "select C where B = 'ProductName' order by A desc limit 10"))

To take the 10 oldest instead of 10 newest, change desc (descending) to asc (ascending).

To use the product name contained in another cell (e.g., F1), insert it by concatenation:

=average(query(A:C, "select C where B = '"&F1&"' order by A desc limit 10"))

Best Answer

Related Solutions

Google-sheets – Google Sheets ARRAYFORMULA not copying form responses

Google Sheets – Select Earliest or Latest Entries of a Subset

Related Topic