SOLVED

data validation on two cells gives result in third cell

Copper Contributor

I hope someone can help, I've tried a few variations without success so far.

 

 

BreedThis is a list of dog breedsChoose from list
Gender(Male or Female)Choose from list
Target Weight I want the weight here e.g.populates from the choices above

 

Example

BREEDDog (kg)**bleep** (kg)
Affenpinscher3  to  73  to  4
Afghan Hound27 to 3223 to 30
Airedale Terrier20 to 2720 to 23

 

I hope the above explains my situation.

26 Replies

@UKAndy 

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)

@UKAndy 


I did not get your need, i got confused with your example.

would you please share your file, so we can help.

@Sergei Baklan 

I have two TABs one labelled (WeightGoals) and the other (Ideally Weight).

 

WeightGoals (TAB)

UKAndy_0-1610195264455.png 

 

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)

UKAndy_1-1610195325040.png

 

 

So selecting the Breed (Airedale Terrier) then the gender (Male) it would give me a Target Weight of 

20 to 23

2021-01-09_12-32-57.jpg

Please review update description

@UKAndy 

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) )

 

@Sergei Baklan 

 

When entering that formula I get a #NAME?  error

2021-01-10_15-06-13.jpg

Ideally Weight (TAB)

2021-01-10_15-08-37.jpg

 

I really appreciate your support in this :)

@UKAndy 

I guess you didn't define names Breed, Range and Gender in workbook. Alternatively you may use proper cell references.

@UKAndy 

This is an implementation of @Sergei Baklan 's formula but using a table.

The final formula is no use without MS365.

@Peter Bartholomew , 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
   )

 

@Sergei Baklan 

Oops! Thanks for the tidying up.  I had been called for a meal and clearly didn't pay enough time to checking.

@Peter Bartholomew , hope you enjoyed the meal

@Sergei Baklan @Peter Bartholomew

 

Thanks to both of you for your support, however, I'm lost.

 

Also, I will be adding additional breeds as and when known so the B, C and D columns will increase.

 

To lessen my confusion see below

@UKAndy 

If you don't mind I converted Ideally Weight to Table. With that if you add/remove data no need to change the formula which could be

=INDEX(IdeallyWeight,MATCH(C5,IdeallyWeight[BREED],0),MATCH(C6,IdeallyWeight[#Headers],0))

@Sergei Baklan 

Brilliant!! AWESOME!!

 

However,

 

What does 'Unprotected Formula' relate to?

Also, for reasons the (B10 Kgs to) and (B11 Target date) is not displaying now, on the WeightGoals Tab, this worked out the Kgs difference + or - from the (Start Weight C4) and the (Target Weight C7) and entered it into C10 then used the value in (C8 Kgs per week) to give a target date in C11.

 

So with Doberman selected weight 15.3Kgs, gaining (1Kg per week), it would give a value of (C10 Kgs to 14.2 - 20.7) , which would give (C11 Target Date 16th April to 28th May).

 

It would workout a + or - value for gain or lose weight.

 

I hope I've explained myself fully, I know what I'm looking for in my head, it's putting it down in words.

 

@UKAndy 

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.

image.png

B10 and B11 - not sure what you'd like to display here. Your initial formula is

=IF(COUNT(C4:C7)<2,0,C4-C7)*TypeMult

it 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?

best response confirmed by UKAndy (Copper Contributor)
Solution

@Sergei Baklan 

 

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.2021-01-12_14-24-43.jpg

 

 

 

@UKAndy , I'll try, but on which version of Excel you are, do you have LET() function available?

@UKAndy 

Somewhere above I noticed that @Sergei Baklan 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!

 

 

 

1 best response

Accepted Solutions
best response confirmed by UKAndy (Copper Contributor)
Solution

@Sergei Baklan 

 

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.2021-01-12_14-24-43.jpg

 

 

 

View solution in original post