FAQ Database Discussion Community


Java Math Formula Loops

java,loops,math,formulas
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...

How to get the last entry containing a certain value in Google Spreadsheet

google-apps-script,google-spreadsheet,google-form,formulas
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...

How do i stop google sheets skipping a row with my formula when a new answer is entered?

google-spreadsheet,formulas
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...

Google Spreadsheet IF formula

google-spreadsheet,formulas
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...

INDEX MATCH VBA (return a value based on two criteria)

excel,vba,excel-vba,lookup,formulas
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...

Calculate percentage average of column of values that match specific text criteria in Google sheets?

google-spreadsheet,formulas
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....

How to SUMIF on a dynamic range that includes blanks?

google-spreadsheet,formulas
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...

VBA - Selection.Formula does not return everything

vba,excel-vba,excel-formula,formulas
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",...

Difference between two dates in excel 2013

excel,formulas
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...

Excel Compare Columns

excel,formulas
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...

how do you validate sub digit values with numbers allowing .5 or .0 without an input mask?

validation,ms-access,input,ms-access-2010,formulas
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...

Use an array formula to check if a similar contract ended in the last 12 months

excel,formulas
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...

OpenOffice Calc displays only 9 numbers after decimal points, how to show more

formatting,openoffice-calc,formulas,libreoffice-calc
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...

SUMIFS function having a sum range as a product of 2 ranges, Array Formulas

arrays,excel,product,formulas,sumifs
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...