Hi,
I'm having an issue getting my spreadsheet to do what I want it to do.
For each month of the year I have 2 cells that I want to use to calculate a formula in a 3rd cell each month. I'll call them Cell1, Cell2 and Cell3 (Cell3 being the formula cell for the calculation).
For each month of a year I want Cell3 to calculate figures from Cell2 based on what letter Cell1 has in it.
Cell1 each month has either N, U, C or CU in it or it is "blank". I use the term blank with quotation marks because the cell isn't necessarily blank, it will contain a formula to equal Cell1 from the previous month. If the previous month's Cell1 is an empty cell then it will return a blank cell/zero.
e.g:
January's Cell1 is called A1, February's is called F1 and March's is called M1. February's cell will contain the formula =A1 and March's cell will contain the formula =F1.
If January has an "N" in A1 then F1 and M1 will also have an N in it because the cells' formulas equal the previous month's. If January was an empty cell and had no letter in it, then F1 and M1 would return a blank cell but wouldn't actually be an empty cell because they contain a formula.
For each month I want Cell3's formula to multiply Cell2 by 13.80 IF Cell1 has an N or U in it and multiply Cell2 by 10.80 IF Cell1 has a C or CU in it. If Cell1 has a "blank" cell then I just want Cell3 to return a zero.
The formulas I currently have for Cell3 for January and February is:
Jan: =IF(OR(D11={"N","U"}),K11*13.8,IF(OR(D11={"C","CU"}),K11*10.8,IF(D11="","")))
Feb: =IF(OR(P11={"N","U"}),AH11*13.8,IF(OR(P11={"C","CU"}),AH11*10.8,IF(P11="","")))
In January, Cell1 is an empty cell so the formula for Cell3 in January (the 1st one) works fine because it recognises that D11 is an empty cell.
For Feb, Cell1 (P11) is "blank" because it equals Jan's D11 cell which is empty but contains the formula =D11 so Cell3 for Feb returns a "FALSE" value when I want it to return a zero/blank cell.
I hope that all makes sense! How do I resolve this?
Kind regards,
Sally
Bookmarks