I Need a Spreadsheet to Feed the Dogs! :)

[jdpro]

Hi Mr. Excel Forum members,

I have two dogs and need to feed them variable amounts of two types of food.

Dog No.1 needs 762 kcal/day
Dog No.2 needs 311 kcal/day

Kibble is 4 kcal/gram
Canned is 1kcal/gram

What I would like to do is feed variable amounts of the two types of food for each dog while maintaining the needed calories. I would like to be able to add some of one food to the dish on the scale, enter that amount (in grams) in the spreadsheet, and then have Excel calculate the number of grams of the other food needed to make up the caloric difference. Sometimes I might want to start with the canned food and other times start with the kibble. I might give one dog 1/3 of his food as canned but only 1/4 of canned for the other dog on one day, and then change these proportions the next day.

How would you recommend I go about doing this?

Thank you!

 

[fhqwgads]

maybe like this?

00 HTML Conversions.xlsm
	A	B	C	D
1	Dog1		Dog2
2	Kibble	Canned	Kibble	Canned
3
Sheet3

Cell Formulas
Range	Formula
A3	A3	=IF(ISFORMULA(B3),"",(762-B3*1)/4)
B3	B3	=IF(ISFORMULA(A3),"",(762-A3*4)/1)
C3	C3	=IF(ISFORMULA(D3),"",(311-D3*1)/4)
D3	D3	=IF(ISFORMULA(C3),"",(311-C3*4)/1)

cells should stay blank until you enter numbers. entering something in a3 should calculate a4 and vice versa hopefully

 

[jdpro]

This works, thank you! I will have to make sure that after entering amounts in a field that I should quit the book without saving because when I enter an amount, it replaces the formula in the cell.

 

[jtakw]

Hi,

Perhaps you can set it up this way, then you can save the book without affecting the formulas.
Also, I used cell references for Grams required, grams for Kibble, grams for Canned, incase these numbers changes in the future:

Input one or the other in Row 4, Row 5 will give you what's required of the other
B5 formula copied to D5
C5 formula copied to E5

00 HTML Conversions.xlsm
	A	B	C	D	E	F	G	H
1			kcal/day		kcal/day		Kibble kcal/gram	Canned cal/gram
2		Dog1	762	Dog2	311		4	1
3		Kibble	Canned	Kibble	Canned
4	Input grams	150		50
5	Required grams		162		111
Sheet3

Cell Formulas
Range	Formula
B5,D5	B5	=IF(B4="",(C2-C4*$H2)/$G2,"")
C5,E5	C5	=IF(C4="",(C2-B4*$G2)/$H2,"")

 

[jdpro]

I'm going through marking solutions to my questions, but it looks like something is broken here... Is this something that is fixable on the forum? I would like to be able to access this again.

Thank you!

Jdpro

 

[Dave Patton]

possible suggestions

1. you could post an extract of your sheet with the forum's tool XL2BB and ask additional questions that you may have
2. try sending a message to jtakw
3. if you do not have the spreadsheet, provide complete information and expected results and someone may help

 

[Gokhan Aycan]

I don't know how you solved it before, but just prepared this:

Only change the green cells.

book1.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2		kcal/day	Type 1	Grams	Kcal	Type 2	Grams	Kcal	Total Kcal
3	Dog 1	762	Canned	100	100	Kibble	165.5	662	762
4	Dog 2	311	Kibble	50	200	Canned	111	111	311
5
6		kcal/grams
7	Kibble	4
8	Canned	1
9
10
11
12
13
Sheet5

Cell Formulas
Range	Formula
E3:E4	E3	=IF(C3="Kibble",D3*$B$7,D3*$B$8)
F3:F4	F3	=IF(C3="Kibble","Canned","Kibble")
G3:G4	G3	=IF(F3="Kibble",H3/$B$7,H3/$B$8)
H3:H4	H3	=B3-E3
I3:I4	I3	=E3+H3

Cells with Data Validation
Cell	Allow	Criteria
C3:C4	List	=$A$7:$A$8

 

[jdpro]

That looks fantastic Gokhan, thanks for your input. With some digging I found the old spreadsheet. The formulas given by fhqwgads are as follows:

For the little dog
Kibble cell A3: =IF(ISFORMULA(B3),"",(311-B3*1)/4)
Canned cell B3: =IF(ISFORMULA(A3),"",(311-A3*4)/1)

The bigger dog's cells are the same except replacing 762 for 311 in the formula.

This will serve to preserve the answer fhqwgads provided, which is an elegant solution.

I would make multiple copies on the sheet and always leave one blank so the formulas are saved.

Thanks!

​

 

[Peter_SSs]

it looks like something is broken here... Is this something that is fixable on the forum?

It related to the forum changing its underlying software a couple of years ago. Not all screen shots were able to be transformed to the new format automatically. However, I have done the couple above manually for you.

 

[jdpro]

Peter_SSs, thank you for taking the time and trouble to do that! You guys are fantastic.