excel-formula

If column A contains the amounts to be reimbursed, and column B contains the “yes/no” indicating whether the reimbursement has been made, then either of the following will work, though the first option is recommended: =SUMIF(B:B,”No”,A:A) or =SUMIFS(A:A,B:B,”No”) Here is an example that will display the amounts paid and outstanding for a small set of … Read more

To solve this problem in Excel, usually I would just type in the literal row number of the cell above, e.g., if I’m typing in Cell A7, I would use the formula =A6. Then if I copied that formula to other cells, they would also use the row of the previous cell. Another option is … Read more

Custom format [>=1000]#,##0,”K”;0 will give you: Note the comma between the zero and the “K”. To display millions or billions, use two or three commas instead.

Assuming your row number is in B1, you can use INDIRECT: =INDIRECT(“A” & B1) This takes a cell reference as a string (in this case, the concatenation of A and the value of B1 – 5), and returns the value at that cell.

You need to use the SUBTOTAL function. The SUBTOTAL function ignores rows that have been excluded by a filter. The formula would look like this: =SUBTOTAL(9,B1:B20) The function number 9, tells it to use the SUM function on the data range B1:B20. If you are ‘filtering‘ by hiding rows, the function number should be updated … Read more

You can name cells. This is done by clicking the Name Box (that thing next to the formula bar which says “A1” for example) and typing a name, such as, “myvar”. Now you can use that name instead of the cell reference: = myvar*25