Trying to come up with a nesting formula for multiple cells

headspinning · Aug 16, 2011

I am trying to come up formula that will give me what I want based on what the answer in two previous columns are. I think my issue is that I am only wanting one of the previous columns to change values....

C / D / E / F / G / H
# of kids per family/ Time In AM/ Time Out AM/ HOURS/ 1/2 hours/ CHARGE

1 / 7:15 AM / 8:00 AM / 0.75 / 2
2 / 7:30 AM / 8:00 AM / 0.50 / 1
2/ 7:45 AM / 8:00 AM / 0.25 / 1
3 / 7:14 AM / 8:00 AM / 0.77 / 2
3 / 7:14 AM/ 8:00 AM / 0.77 / 2
3/ 7:14 AM / 8:00 AM / 0.77 / 2

Our current billing plan allows for a different half hour and hour charge depending on the number of kids per family. What I would like to do is come up with a formula for column H (Charge) that will calculate that depending on how many kids they have in their family. I can get it to work for 1 child but after that I just keep getting error messages.

Here is the formula I was using:
=IF(AND(C3=1,G3=1),2.80,3.80)

What do I need to add to that so that when C3=2 & G3=1 then I get 2.25 and if not 3.20 and if C3=3 & G3=1 then I get 1.87 and if not 2.67? Can I even do that since I want G3=1 in all cases and just the value in C to change?

Thank you for your help.
Head Spinning :D

jbeaucaire · Aug 16, 2011

How about:

=IF(G3=1, LOOKUP(C3, {1,2,3}, {2.25, 1.87, 2.80}), 3.80)

If that's not it, create an easy to read table of all the "combinations". Remember, each combination should then have one matching "rate", not two.

headspinning · Aug 16, 2011

Thanks!! It's close to doing what I need it to. It at least gives me a better idea of what kind of formula I need then what I had.

The way our school bills for pre and post care is very tricky to build a spreadsheet for. If a family only has 1 child then their billing is $2.80 for a half hour and $3.80 for one hour, If they have 2 children they are billed $2.25 for a half hour and $3.20 for an hour per child, if they have 3 children they are billed $1.87 for a half hour and $2.67 for an hour per child. That is what I am trying to get the formula to calculate for me. I have the number of children per family in column C and the number of half hours that they are in child care in column G. In column H I am trying to get the formula to calculate all the different options. If there is 1 child for half an hour or an hour, if there is two children for half and hour or an hour, and if there is 3 children for half and hour or an hour all at their different rates. If any of that made sense I'd love more ideas, otherwise I will keep playing with what you gave me above. Thank you so much for the help.

headspinning · Aug 16, 2011

YAY!!!! I think I got it using what you gave me. I just tweaked it a little bit.

=IF(H3=1, LOOKUP(C3, {1,2,3}, {2.8,2.25,1.87}),IF(H3=2,LOOKUP(C3,{1,2,3},{3.8,3.2,2.67}),IF(H3=0,0)))

Thank you!!!!

headspinning · Aug 16, 2011

OK, one more quick question....

Now I am making the column for afternoon care (which can go longer - up to 3 hours or 6 half hours). Here is the formula I am using....

<style>  </style> =IF(M3=1,LOOKUP(C3, {1,2,3}, {2.8,2.25,1.87}),IF(M3=2,LOOKUP(C3,{1,2,3},{3.8,3.2,2.67}),IF(M3=3,LOOKUP(C3,{1,2,3},{6.6,5.45,4.54}),IF(M3=4,LOOKUP(C3,{1,2,3},{7.6,6.4,5.34}),IF(M3=5,LOOKUP(C3,{1,2,3},{10.4,8.65,7.21}),IF(M3=6,LOOKUP(C3,{1,2,3},{11.4,9.6,8.01}),IF(M3=0,0)))))))
I just took the same thing I used in the earlier questions and adapted it for the afternoon. I changed the number of half hours column from H to M and still told it to look back at the C column to see how many siblings were there and then gave it the corresponding prices. What am I doing wrong? It keeps giving me the answer "FALSE" in my cell.
Thanks.

T. Valko · Aug 16, 2011

headspinning said:
OK, one more quick question....

Now I am making the column for afternoon care (which can go longer - up to 3 hours or 6 half hours). Here is the formula I am using....

<STYLE>  </STYLE>=IF(M3=1,LOOKUP(C3, {1,2,3}, {2.8,2.25,1.87}),IF(M3=2,LOOKUP(C3,{1,2,3},{3.8,3.2,2.67}),IF(M3=3,LOOKUP(C3,{1,2,3},{6.6,5.45,4.54}),IF(M3=4,LOOKUP(C3,{1,2,3},{7.6,6.4,5.34}),IF(M3=5,LOOKUP(C3,{1,2,3},{10.4,8.65,7.21}),IF(M3=6,LOOKUP(C3,{1,2,3},{11.4,9.6,8.01}),IF(M3=0,0)))))))
I just took the same thing I used in the earlier questions and adapted it for the afternoon. I changed the number of half hours column from H to M and still told it to look back at the C column to see how many siblings were there and then gave it the corresponding prices. What am I doing wrong? It keeps giving me the answer "FALSE" in my cell.
Thanks.

If you create a table like this:

Book1

A

B

C

D

5

1

2

3

6

1

2.8

2.25

1.87

7

2

3.8

3.2

2.67

8

3

6.6

5.45

4.54

9

4

7.6

6.4

5.34

10

5

10.4

8.65

7.21

11

6

11.4

9.6

8.01

Sheet1

Then you can use a much shorter formula like this:

=IF(M3=0,0,VLOOKUP(M3,A5:D11,MATCH(C3,A5:D5),0))

headspinning · Aug 16, 2011

Thanks. That would have been much easier. I did figure out that it wasn't that formula that is giving me the '"false" result. My formula for the column before it (L) must be wrong. I was getting a numeric value with the formula I was using, but apparently it is the wrong formula.

Now I am working on figuring out how to write the formula so that when L3=0 then M3=0, when L3 is between 0.01 & 0.5 M3 will = 1, when L3 is between 0.51 & 1.0 M3 will =2, when L3 is between 1.01 & 1.5 M3 will = 3, when L3 is between 1.51 and 2 M3 will = 4, when L3 is between 2.01 & 2.5 M3 will = 5, when L3 is between 2.51 & 3 M3 will = 6.

T. Valko · Aug 16, 2011

headspinning said:
Thanks. That would have been much easier. I did figure out that it wasn't that formula that is giving me the '"false" result. My formula for the column before it (L) must be wrong. I was getting a numeric value with the formula I was using, but apparently it is the wrong formula.

Now I am working on figuring out how to write the formula so that when L3=0 then M3=0, when L3 is between 0.01 & 0.5 M3 will = 1, when L3 is between 0.51 & 1.0 M3 will =2, when L3 is between 1.01 & 1.5 M3 will = 3, when L3 is between 1.51 and 2 M3 will = 4, when L3 is between 2.01 & 2.5 M3 will = 5, when L3 is between 2.51 & 3 M3 will = 6.

Try this...

=IF(AND(L3>0,L3<=3),MATCH(L3,{0.01,0.51,1.01,1.51,2.01,2.51}),0)

headspinning · Aug 16, 2011

You are AMAZING!!!!! Thank you. I think I have the hard parts done now thanks to all of you!!!

T. Valko · Aug 16, 2011

headspinning said:
You are AMAZING!!!!! Thank you. I think I have the hard parts done now thanks to all of you!!!

You're welcome. Thanks for the feedback!

Trying to come up with a nesting formula for multiple cells

headspinning

New Member

Excel Facts

jbeaucaire

Well-known Member

headspinning

New Member

headspinning

New Member

headspinning

New Member

T. Valko

Well-known Member

headspinning

New Member

T. Valko

Well-known Member

headspinning

New Member

T. Valko

Well-known Member

Similar threads

Forum statistics

Share this page

Trying to come up with a nesting formula for multiple cells

New Member

Excel Facts

Well-known Member

New Member

New Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock