Formula Assistance Please - IF statement the right formula?

[mins78]

Hi,

I have made a calculator which has multiple variables based on whether someone is Single or Married (defacto goes under same heading), and number of kids. Hence if someone is single with one child it would be a different outcome (cell number) than if they were married with 2 kids.

Then just to complicate things even more, if someone has more than 3 kids in both single and married cases I would need them to take the value from cell "x - being the additional child expense" and multiply by number of kids over 3... so eg:

Married couple 5 kids
Direct to Cell "a" which has value of $2000 based on a married couple with 3 kids then PLUS cell "x with value of say $300" times by 2 extra kids. = so total should be $2600.

It needs to work for all variables including no kids and up to say 6 -7 kids

I hope this makes sense.

 

[wideboydixon]

Something like this?

Book1
	A	B	C	D	E	F
1	Status	No children	1 Child	2 Children	3 Children	Additional child expense
2	Single	600	800	1200	1600	200
3	Married	800	1000	1500	2000	300
4
5
6
7	Status	Single
8	# Children	7
9	Expense	2400
Sheet1

Cell Formulas
Range	Formula
B9	=INDEX($B$2:$E$3,MATCH($B$7,$A$2:$A$3,0),MIN($B$8,3)+1)+IF($B$8>3,INDEX($F$2:$F$3,MATCH($B$7,$A$2:$A$3,0))*($B$8-3),0)

WBD

 

[mins78]

This is great but wow I was kinda far off. I thought I was not too bad at excel but this has totally confused me. I have no idea how to use this information within the current spreadsheet I have made. Essentially I have a master sheet where I enter the income in the correct sections, choose married/single and the number of kids. Then I have a separate page in the background for each lender.

Each lenders page pulls the information re marital status and number of kids to that page but how do I reference the above to use the information in the relevant cell?

I can copy and paste the table and formula etc but for it to automatically pick up the number of kids and marital status. I have NEVER used "Index" before which is why I am so confused. Not sure what I would need to change in the formula to make it work.

Thanks for the awesome reply and help

 

[mins78]

Just to Clarify on each lender sheet the marital status pulls across into cell I3 (same for all pages). The number of kids pulls across into cell D21 on each sheet.

 

[wideboydixon]

OK. Let's assume you put the expenditure table on a sheet call "Expenditure" like this:

Book1
	A	B	C	D	E	F
1	Status	No children	1 Child	2 Children	3 Children	Additional child expense
2	Single	600	800	1200	1600	200
3	Married	800	1000	1500	2000	300
Expenditure

Now, if marital status is in I3 and number of kids is in D21 the formula becomes:

=INDEX(Expenditure!$B$2:$E$3,MATCH($I$3,Expenditure!$A$2:$A$3,0),MIN($D$21,3)+1)+IF($D$21>3,INDEX(Expenditure!$F$2:$F$3,MATCH($I$3,Expenditure!$A$2:$A$3,0))*($D$21-3),0)

The INDEX/MATCH formulas are pretty standard but in this case you could swap for VLOOKUP if necessary:

=VLOOKUP($I$3,Expenditure!$A$2:$F$3,MIN($D$21,3)+2,FALSE)+IF($D$21>3,VLOOKUP($I$3,Expenditure!$A$2:$F$3,6,FALSE)*($D$21-3),0)

WBD

 

[mins78]

Is there a way that I could put the expenditure table on each page and reference back? Only because each lender has different figures they use. Hence it won't work using just one sheet "expenditure".

I was thinking if could put the table onto the lenders sheet at the same cell and this way when the lenders update their expense guidelines, I just need to update the individual lenders table as per what you have above.

If this works I would create your table starting at cell C33 for "Status" Cell and go 3 down to c35 and across to H33/H35. This would be perfect!

 

[wideboydixon]

OK. Then:

=VLOOKUP($I$3,$C$34:$H$35,MIN($D$21,3)+2,FALSE)+IF($D$21>3,VLOOKUP($I$3,$C$34:$H$35,6,FALSE)*($D$21-3),0)

WBD

 

[mins78]

Thanks for your help WBD but not working

It changes the value for Single vs married but then doesn't change the figure based on the number of kids entered.

 

[wideboydixon]

Oh. I tested this myself on a local workbook and it was working as expected :-|

WBD

 

[mins78]

Hi WMB,

I fiddled around with it. For some reason it had changed the D reference to 22 instead of 21. Changed it back and it works perfectly. You are the ultimate Excel wizard. Thank you so much