I have a column of numbers that I want to sum, but only under certain matched conditions, thus I've used SUMIF and SUMIFS with success. Now I want to make the range dynamic. Here's the working, non-dynamic, function: =SUMIF(B2:B60, "CC/*", A2:A60) Column B contains values that begin with "CC/" and...

How can I calculate the average percentages in the screenshot below? I've tried a number of different formulas such as: sumif/countif average(if) averageif averageifs arrayformula(average....) But I get, Error: Div/0, #value or wrong results I would appreciate your help. I'm using the latest google spreadsheets available. See below the table....

I'm doing some work for a client where they receive a weekly report of customers and regions (two columns, A for Customer & B for Region, in a worksheet). * I've omitted the other columns in this spreadsheet for the sake of clarity They need to match the price on...

I have three columns in my table: column G - maturity date, column H - payment date and column I - number of days between maturity date and today IF(G11="";"";G11-today()). What I need to do is to make cells in column I not display anything until I enter data in...

I'm trying to minimize creating uneccessary tables in Excel, and instead of creating a table to multiply columns A and B, can I do it within the IF formula? Perhaps using Array formulas? I tried Ctrl + Shift + Enter to enter the following formula: SUMIFS($A$1:$A$10*$B$1:$B$10,$C$1:$C$10,"Value") however it did not...

My clients are getting frustrated at typing in 000.5 or 001.0 in a number of days field due to being forced to use an input mask to prevent invalid values. I have managed to sort out validation for a range of numerical values 180.0 - 0.5 allowing only half or...

I have 2 columns, one with 2800 records and another with 20,001 records. I am looking to find out if any of column I (2800 records) are listed in column BB (20,000 records), if it does = TRUE, if it doesn't = FALSE. I tried the formula below and was...

I'm currently just troubleshooting a report spreadsheet I've written. The logic I had written isn't working, so I thought I'd see if I could ask for some help here. Essentially, I have a list of contracts, a list of products, a list of end and start dates, and various other...

I am in a situation of dealing with numbers when there is need to display the numbers as is on the excel sheet. Currently 37.6172080480001 is being displayed as 37.617208048 Seems like the sheet by default only displays 9 digits after the decimal points. How can that be updated to...

I have an answer spreadsheet linked to a Google form. I wish to get the last date that and answer was submitted. Lets say I my entries look like this : DATA : A B 1 Date String 2 2015-05-09 Abb 3 2015-05-11 Bcc 4 2015-05-12 Cdd 5 2015-05-20 Bcc...

Say I have a very long formula in one of my cells in Excel: =IF(SUM(D3:D6)>1,"A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X-Y-Z 01", IF(SUM(D3:D6)>1,"A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X-Y-Z 02", IF(SUM(D3:D6)>1,"A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X-Y-Z 03", IF(SUM(D3:D6)>1,"A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X-Y-Z 04", IF(SUM(D3:D6)>1,"A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X-Y-Z 05", IF(SUM(D3:D6)>1,"A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X-Y-Z 06",...

I want to calculate the time difference between two dates with the following criteria: 1) excluding sundays and public holidays 2) TIMING: Mon to Fri = 7 AM to 7 PM & Sat = 7 AM to 3 PM I tried a many formulas and nothing works for me. please...

how do you add formula n! = 1 * 2 * 3 * i in java that it will be in a loop. An example is if the user inputs 5 the output will be 1 * 2 * 3 * 4 * 5 = 120. If you input 4...

Every time a new row of answers is added the formula on another shit will skip the already referenced rows where the new data has just been added. If i manually drag it down across a number of fields it updates properly and shows the correct number. However if i...