I need help making a formula. I will do my best to describe.
The letters (A-I) refer to separate columns.
A-B=C to be divided by D the result is column E. also
A-F=G to be divided by H the result is column I.
Sometimes A-B or A-F will equal 0 which can not be divided by D or H. When that happens I
want C and G to to automatically equal .01 instead of zero so that i can still have a scale of numeric values negative or positive.
After all this is done I want Excel to compare the results of E and I and give me the lowest value between the two (if there are 2) or just the one value that exists as column J.
When either columns B & F are blank there is no need to continue solving for that portion of the formula. I just want it to move on to the next portion.
E and I compared, if both have a value the lowest is selected to become J (final score). If only one of the two E or I exists I want that sole value reflected in column J.
I need the formula to be spatially correct as I am still a beginner to Excel.
"Why column V and AI insist on turning blank cells into zeros I don't understand."
That's because formula wise, blanks are the same as 0's. Type an "=" in one cell, select an empty cell, hit enter and the result will be 0.
The following formula's will replace 0's and errors with blanks:
I think it's best to create a table to alter the values. You can place this table out of sight or on another sheet. But the idea is to put the values 1-12 in one column and 1-3.75 in the second column. Then use VLOOKUP to pull the data from that table.
So let's say sheet2 range A1:B12 is used for the table. Then the formula in the new column would look like:
This way you can easily alter the values in the second column of the table without having to change the formula's.