I Need a Spreadsheet to Feed the Dogs! :)

jdpro

Board Regular
Joined
May 1, 2016
Messages
81
Office Version
  1. 365
Platform
  1. MacOS
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!
 
Last edited:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
maybe like this?

00 HTML Conversions.xlsm
ABCD
1Dog1Dog2
2KibbleCannedKibbleCanned
3    
Sheet3
Cell Formulas
RangeFormula
A3A3=IF(ISFORMULA(B3),"",(762-B3*1)/4)
B3B3=IF(ISFORMULA(A3),"",(762-A3*4)/1)
C3C3=IF(ISFORMULA(D3),"",(311-D3*1)/4)
D3D3=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
 
Last edited by a moderator:
Upvote 0
Solution
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.
 
Upvote 0
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
ABCDEFGH
1kcal/daykcal/dayKibble kcal/gramCanned cal/gram
2Dog1762Dog231141
3KibbleCannedKibbleCanned
4Input grams15050
5Required grams 162 111
Sheet3
Cell Formulas
RangeFormula
B5,D5B5=IF(B4="",(C2-C4*$H2)/$G2,"")
C5,E5C5=IF(C4="",(C2-B4*$G2)/$H2,"")
 
Last edited by a moderator:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
I don't know how you solved it before, but just prepared this:

Only change the green cells.

book1.xlsm
ABCDEFGHIJ
1
2kcal/dayType 1GramsKcalType 2GramsKcalTotal Kcal
3Dog 1762Canned100100Kibble165.5662762
4Dog 2311Kibble50200Canned111111311
5
6kcal/grams
7Kibble4
8Canned1
9
10
11
12
13
Sheet5
Cell Formulas
RangeFormula
E3:E4E3=IF(C3="Kibble",D3*$B$7,D3*$B$8)
F3:F4F3=IF(C3="Kibble","Canned","Kibble")
G3:G4G3=IF(F3="Kibble",H3/$B$7,H3/$B$8)
H3:H4H3=B3-E3
I3:I4I3=E3+H3
Cells with Data Validation
CellAllowCriteria
C3:C4List=$A$7:$A$8
 
Upvote 0
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!

 
Upvote 0
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.
 
Upvote 0
Peter_SSs, thank you for taking the time and trouble to do that! You guys are fantastic.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top