I apologize in advance for the lengthy post but wanted to explain as much as I think is needed.
End goal is to have a formula in cell M16 that will total cells M6 through M15. Cells M6 - M15 will either have a manual entry OR, and more likely, not have a manual entry, but for calculation purposes (not for display purposes) carry the value entered in cell M5.
Values in M6 - M15 are used in column N with this, or similar formula referencing the appropriate M and D cells
=IF(M6="",$M$5*D6,$M6*D6) (Note the reference to D6. If D6 is empty, the value in N6 will also be empty)
If there is no manually entered value in any cell M6 - M15, it will display as empty, but use the value in M5 for calculation in M16 with the two exceptions below
Let's say M5 has the value of 3. The value in M5 will apply to calculations representing each cell M6 - M15 unless/ or
1. A specific cell M6 - M15 has a manually entered number
2. A specific call D6 - D15 is empty. Column D is used to calculate several other equations but if it's empty, all calculations referencing column D should be null.
For example calculations, let's say:
M5 has a value of 3
M6 has a manually entered value of 1 (for calculation purposes, this will count as 1)
M7 and M8 have a manually entered value of 2 (for calculation purposes, these each will count as 2)
M9 - M12 have no manually entered value and are left empty (for calculation purposes, these cells will take on the value in M5, in this case will count as 3)
M13 - M15 will not be included in the M16 calculation because D13 - D15 are empty
So, the total displayed in M16 would be 17 because
M6 = 1 + M7 = 2 +M8 = 2 + M9 = 3 + M10 = 3 + M11 = 3 + M12 = 3
M13, M14 and M15 would not add any value because D13 - D15 are empty.
I need a formula for cell M16 that would calculate as outlined above.
Maybe here's another way to outline it:
The first thing the formula should look at is to see whether or not D has any value =IF(D6="") If this is true then M6 should carry the value of 0 when calculating the total in M16.
Thank you in advance
End goal is to have a formula in cell M16 that will total cells M6 through M15. Cells M6 - M15 will either have a manual entry OR, and more likely, not have a manual entry, but for calculation purposes (not for display purposes) carry the value entered in cell M5.
Values in M6 - M15 are used in column N with this, or similar formula referencing the appropriate M and D cells
=IF(M6="",$M$5*D6,$M6*D6) (Note the reference to D6. If D6 is empty, the value in N6 will also be empty)
If there is no manually entered value in any cell M6 - M15, it will display as empty, but use the value in M5 for calculation in M16 with the two exceptions below
Let's say M5 has the value of 3. The value in M5 will apply to calculations representing each cell M6 - M15 unless/ or
1. A specific cell M6 - M15 has a manually entered number
2. A specific call D6 - D15 is empty. Column D is used to calculate several other equations but if it's empty, all calculations referencing column D should be null.
For example calculations, let's say:
M5 has a value of 3
M6 has a manually entered value of 1 (for calculation purposes, this will count as 1)
M7 and M8 have a manually entered value of 2 (for calculation purposes, these each will count as 2)
M9 - M12 have no manually entered value and are left empty (for calculation purposes, these cells will take on the value in M5, in this case will count as 3)
M13 - M15 will not be included in the M16 calculation because D13 - D15 are empty
So, the total displayed in M16 would be 17 because
M6 = 1 + M7 = 2 +M8 = 2 + M9 = 3 + M10 = 3 + M11 = 3 + M12 = 3
M13, M14 and M15 would not add any value because D13 - D15 are empty.
I need a formula for cell M16 that would calculate as outlined above.
Maybe here's another way to outline it:
The first thing the formula should look at is to see whether or not D has any value =IF(D6="") If this is true then M6 should carry the value of 0 when calculating the total in M16.
Thank you in advance