formula tocalculate "if" problems

[HNR]

Hi
I have the following spreadsheet layout:

Column: A, B, C, D, E.
Actual Payment, Concession, Published Fee, Variance 1. (Shortfall), Variance 2. (Subsidy).
Row 1 7000 9000 10000 2000 1000
Row 2 7000 0 10000 3000 0
Row 3 9000 9000 10000 0 1000
Row 4 10000 0 10000 0 0

these figures represent fees paid by older persons in a frail care facility run by a non-profit organization.
In Rows 1-4: C1-C4 is the published rate in all Rows; 10000.
In Row 1: Management has made a concession based on circumstances of 1000-B1.This is considered a subsidy-E1. However the person is still short paying 2000, hence 7000 in cell A1 and a shortfall of 2000 in cell D1.
In Row 2: Client did not qualify for a subsidy, and is paying 3000 short of the published fee.
In Row 3: Client gets a subsidy of 1000 and pays per agreement. So only a subsidy is recorded.
Row 4: is self explanatory.
Help needed: a formula for Col D and Col E to calculate shortfall and / or subsidy in each case

Thanks for help. Hennie Richards

[Ron Coderre]

Using your sample data:

	A	B	C	D	E
1	Actual Payment	Concession	Published Fee	Variance 1. (Shortfall)	Variance 2. (Subsidy).
2	7,000	9,000	10,000	2,000	1,000
3	7,000	0	10,000	3,000	0
4	9,000	9,000	10,000	0	1,000
5	10,000	0	10,000	0	0

This formula, copied down, calculates the subsidy:

Please Login or Register  to view this content.

And this formula, copied down, calculates the variance:

Please Login or Register  to view this content.

Is that something you can work with?

[HNR]

Hi Ron

Give me a moment to test the formulas please.

Hennie

[HNR]

HI Ron
The formulas are working spot on! I dare say you knew they would!

Thanks for your help

Greetings
Hennie

[Ron Coderre]

Glad you got something you can use!

If that resolves your issue, please take a moment to mark this thread as SOLVED (from the Thread Tools menu)