Forum Discussion
data validation on two cells gives result in third cell
Hi,
The Weight Tracker is look great 🙂
So to explain, taking an Old English, she weighed in at 15.3Kg, her target weight is 23 to 27Kgs
Which means she is between 7Kg and 11.7Kg under her true weight.
In C10 it would give the difference from the weight and target weight
C11 it would give the date to the target weight in this case 23 to 27Kg if she gains 1 Kg a week, 7 to 11.7 weeks
Sorry if am not explaining myself right.
I touched nothing but formula.
Unprotected formula - cells in your sheet are unlocked, don't know why. That means if you protect the sheet, these cells will be editable. That's what the alert says about. I locked the cell, but only this very cell, since don't know what is the logic behind unlocking. To lock/unlock select cell(s), Ctrl+1 and choose desired setting in the Protection tab.
B10 and B11 - not sure what you'd like to display here. Your initial formula is
=IF(COUNT(C4:C7)<2,0,C4-C7)*TypeMultit counts numbers in C4:C7. Since only Start Wight is the number in the range, it returns 1, thus result is zero. What it shall be here?
Hi,
The Weight Tracker is look great 🙂
So to explain, taking an Old English, she weighed in at 15.3Kg, her target weight is 23 to 27Kgs
Which means she is between 7Kg and 11.7Kg under her true weight.
In C10 it would give the difference from the weight and target weight
C11 it would give the date to the target weight in this case 23 to 27Kg if she gains 1 Kg a week, 7 to 11.7 weeks
Sorry if am not explaining myself right.
I have placed the MEDIAN formula with SergeiBaklan 's (kg and kg0) and confirmed the results are the same.
That is the idea of the formula with MEDIAN. It deals with the three options without requiring the use of conditional branches. The limitation is that the approach cannot be applied to an array of actual weights, since, like other aggregations, MEDIAN will give a single result for the entire array. I haven't looked at the latest workbook in any detail, so I do not know whether such array calculation will be needed.
UKAndy , you are welcome.
I didn't dig very deep, but current formula returns the same - underweight if less than min, overweight if over max and zero overwise. PeterBartholomew1 , that was an idea?
In any case, please suggest how exactly the logic shall be changed.
Back to initial file, "Kgs to..." could be
=LET(del, "to", startW, $C$4, targetW, $C$7, pos, SEARCH(del, targetW), minW, --TRIM(IF(ISNUMBER(pos), LEFT(targetW,pos-1), targetW)), maxW, --TRIM(IF(ISNUMBER(pos), RIGHT(targetW,LEN(targetW)-pos-1), targetW)), kg, IF(minW > startW, minW-startW, IF(maxW<startW, maxW-startW, 0)), kg )and Target Date
=IFERROR(IF(C8=0,"",C3+ABS(C10)/C8*7),C3)UKAndy , I'll try, but on which version of Excel you are, do you have LET() function available?
