Forum Discussion
data validation on two cells gives result in third cell
I hope someone can help, I've tried a few variations without success so far.
| Breed | This is a list of dog breeds | Choose from list |
| Gender | (Male or Female) | Choose from list |
| Target Weight | I want the weight here e.g. | populates from the choices above |
Example
| BREED | Dog (kg) | **bleep** (kg) |
| Affenpinscher | 3 to 7 | 3 to 4 |
| Afghan Hound | 27 to 32 | 23 to 30 |
| Airedale Terrier | 20 to 27 | 20 to 23 |
I hope the above explains my situation.
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.
26 Replies
Somewhere above I noticed that SergeiBaklan had recommended the conversion of your reference data to a Table. I would recommend the same but I take it further. I use Tables and structured references and try to avoid any direct referencing using cell addresses. In the workbook that Sergei was kind enough to correct I had converted the data to a table but it wasn't obvious because I tend to remove the stripes and filters.
I have had an idea for comparing the animal weight with a band of normal weight that does not depend upon constructs with nested IFs. If you consider the 3 weights, breed maximum, breed minimum and actual weight, the formula
= MEDIAN( actual, minimum, maximum )
will return the closest bound. The percentage over/underweight is then given by
= StartWeight/result - 1
If you introduce number formatting to the result, you can add the "over/underweight" to the displayed value, depending upon the sign of the result. If the dog's weight is "Within normal bounds", the formula will give zero and the appropriate message displayed.
p.s. Glad to see from the later document that my Newfoundland dog is spot on target!
This is an implementation of SergeiBaklan 's formula but using a table.
The final formula is no use without MS365.
PeterBartholomew1 , thank you.
I'd modify a bit
= LET( split, FIND("to", TargetWeight), lowerLimit, --TRIM(LEFT( TargetWeight, split-1 )), upperLimit, --TRIM(RIGHT(TargetWeight,LEN(TargetWeight)-split-2)), result, IF( StartWeight < lowerLimit, TEXT(1 - StartWeight/lowerLimit, "0%") & " under weight", IF( StartWeight > upperLimit, TEXT(1-upperLimit/StartWeight, "0%") & " over weight", "Within normal bounds")), result )Oops! Thanks for the tidying up. I had been called for a meal and clearly didn't pay enough time to checking.
I did not get your need, i got confused with your example.would you please share your file, so we can help.
- Please review update description
If an Example is your source data, you may use INDEX(range,MATCH(),MATCH()) on it. First MATCH to select row from Breed list. Second MATCH to select column, or it simply could be =IF(Gender=Dog,1,2)
I have two TABs one labelled (WeightGoals) and the other (Ideally Weight).
WeightGoals (TAB)
On the weight tab, I enter the start date (8-Jan) and the starting Weight 15.3, below I select the breed from a list populated from the Tab (Ideally Weight).
Ideally Weight (TAB)
So selecting the Breed (Airedale Terrier) then the gender (Male) it would give me a Target Weight of
20 to 23
When as suggested before. Drop-down list for the Breed will be Ideally Weight[Breed] column, for Gender just Male,Female.
If we name as Range data in both Male and Female columns of Ideally Weight, when Target Weight could be taken as
=INDEX(Range, MATCH(Breed, IdeallyWeight[Breed], 0), IF(Gender="Male",1,2) )
