a function that can see the max amount for deductible and stop when it rich maximum amount limit.

[proexecl]

My Best Regards !!!

I am having a hard time with my sheet, I will try to explain it clearly, I have a price in one cell and other prices in another cell but in different column (the reason is, because the prices are a different category) let see :-

in G7 I put a price for consultation 75 $
in H8 I put a price for xray 200
and in I9 i put a price for lap 250 $.

now in this three rows i want to give a discount. what i have done is i make total for each (for G7 at L7, for H8 at L8 and for I9 at L9) now in M7 i gave a discount (L7*0.50) 50% discount also for (L8*0.50) and for (L9*0.50) .

now I want to take a deductible therefore i make after discount in column N
in N7 i put (L7-M7) in N8 (L8-M8) aslo in N9 (L9-M9) .

i want to take a deductible of 20% therefore . in column O, i put in O7 (N7*0.20) in O8 (N8*0.20) and in O9 (N9*0.20) .

now i want to calculate the net therefore, in column P i put in P7 (L7-M7-O7)
in P8 i put in (L8-M8-O8) in P9 i put in (L9-M9-O9) . it means total minus discount minus deductible.

additionally i have extra price, and i want to put it in Q7 50$
and i would like to give it a discount and a deductible.
therefore in R7 i put in (Q7 *0.10) ten % discount , in S7 i put in { (Q7-R7)*0.2 } meaning the amount the price minus the discount multiply by the deductible (20%). and to find the net in column T , i put in T7 (Q7-S7-R7).

at the end to find my total just add the Net and the extra Net (P7+T7) i will put it in column U7 (T7+P7).

Now My question is i want a function to add my deductibles and when it rich more than 50 $ to stop or preventing me to do not inter more.

i have tried some of countif functions in data validation but it did not work.

therefore i kindly need your help Mr,excels.

for more i have attached a picture so than you can understand more.


the area that are in red are the deductibles. again my requirement is i need any function that can see all my deductibles and when they rich 50$ or any amount to stop, or to stop asking for more deductible.

thank you, hope to hear from you shortly .....

if the link for the picture dose not work please copy and past this link in to your browser
http://alkaos.com/view.php?filename=738untitled.bmp

 

[Jonmo1]

So let's do the formulas in Colmn O and P (you should be able to apply to S and T afterwards).

So if the formula in O7 (=N7*0.2) exceeds 50, it should remain at 50.
So O7 should be
=MIN(50, N7*0.2)

Now the amount of (N7*0.2) that is above 50 should be ADDED to the current result of P7 (=L7-M7-O7)

So P7 should be
=L7-M7-O7+MAX((N7*0.2)-50,0)

 

[proexecl]

I am so sorry to say this but, still this doesn't work,the function works for each invoice (each price). what i am requiring is "as you can see from the sheet John Smith have three invoices 75$ (G7) 200 $ (H8) 250 $ (I9) and for medicine 50 $ (Q7) i gave a 50% discount and 20 % deductible and for the medicine 10 % discount and 20 % deductible" now john smith have a deductible of 77.25 $.

but his card is up to 50 $ the extra 27.25 $ is I am going to ask it from the insurance company.

so what i need is not only for one price (invoice) i want to collect all the deductibles from each price and add them than when it rich more than 50 $ transfer the extra to the Net (the insurance company).

hope you understood it.

 

[Jonmo1]

Hmm, still not following.

Can you now make a post of your sheet showing your desired results (manually entered by hand if necessary).
And highlighting the changes you made.
And explain the math (referencing cell addresses) of how you arrived at the desired results.

 

[proexecl]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	*					*	*	*	*	*	*	Analysis Report - Insurance Companies
2	*					*	*	*	*	*	*	*	*	*	*	*	Printed On :00/00/0000
3	Detailed Analysis Report Of Insurance For The Period From											*	*	*	*	Report For : M/s *COOPERATIVE
4	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	PHARMACY				*
5	Date	Invoice	Patient ID	Patient Name	Card	CON	MCT	XRY	LAB	DNT	OPH	TOTAL	DIS	after DIS	DTBL	NET	GROSS	DISC	DTBL	NET	TOTAL
6	0001 - *COOPERATIVE						*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
7	25/12/2011	139089	314	john smith	------	*	75.00	*	*	*	*	75.00	26.25	48.75	9.75	39.00	50.00	5.00	9.00	36.00	75.00
8	25/12/2011	142314	314	john smith	------	*	*	200.00	*	*	*	200.00	70.00	130.00	26.00	104.00	*	0.00	0.00	0.00	104.00
9	25/12/2011	142315	314	john smith	------	*	*	*	250.00	*	*	250.00	87.50	162.50	5.25	157.25	*	0.00	0.00	0.00	157.25
10	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	0.00	0.00	0.00	0.00
11	*	*	*	*	*	*	*	*	As you can see the third deductible is 5.25$ manually							*	*	0.00	0.00	0.00	0.00
12	*	*	*	*	*	*	*	*	entered (but if i use my function =MIN(50; N9*0.2) *) it will							*	*	0.00	0.00	0.00	0.00
13	*	*	*	*	*	*	*	*	be 32.50 $ now if you see the total of all DTBL it's 50$ and							*	*	0.00	0.00	0.00	0.00
14	*	*	*	*	*	*	*	*	the rest of the DTBL 27.25 will go to the NET. *							*	*	0.00	0.00	0.00	0.00
15	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	0.00	0.00	0.00	0.00
AL MAJAL SERVICE MASTER

 

[Jonmo1]

Ok, I think I'm getting it.
Looking for a max accumulated deductible of 50.
And it's accumulated in columns O and S.
In which order do you want to Add the deductibles.
O7+S7+O8+S8+O9+S9 etc..
or
O7+O8+O9+S7+S8+S9 etc..
?
And if the deductible goes over 50 in column O, then the reaminder goes in Column P
If the deductible goes over 50 in column S, then the remainder goes in column T
Is that about right?

 

[proexecl]

Dear jonmo1,

glad that you understand me, and yeah it should be
O7+S7+O8+S8+O9+S9 etc..

also the thing i want you to understand is the patient john smith is not the only
one is going to be on the sheet, there will more patient in that sheet therefore when it go down it should do the same.

i will post other display for more details.

 

[proexecl]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1												Analysis Report - Insurance Companies
2																	Printed On :00/00/0000
3	Detailed Analysis Report Of Insurance For The Period From															Report For : M/s COOPERATIVE
4																	PHARMACY
5	Date	Invoice	Patient ID	Patient Name	Card	CON	MCT	XRY	LAB	DNT	OPH	TOTAL	DIS	after DIS	DTBL	NET	GROSS	DISC	DTBL	NET	TOTAL
6	0001 - COOPERATIVE
7	25/12/2011	139089	314	john smith	******		75.00					75.00	26.25	48.75	9.75	39.00	50.00	5.00	9.00	36.00	75.00
8	25/12/2011	142314	314	john smith	******			200.00				200.00	70.00	130.00	26.00	104.00		0.00	0.00	0.00	104.00
9	25/12/2011	142315	314	john smith	******				250.00			250.00	87.50	162.50	32.50	130.00		0.00	0.00	0.00	130.00
10	28/12/2011	133989	135	john brown	******		60.00					60.00	21.00	39.00	7.80	31.20	25.00	2.50	4.50	18.00	49.20
11	28/12/2011	135689	135	john brown	******			100.00				100.00	35.00	65.00	13.00	52.00		0.00	0.00	0.00	52.00
12	28/12/2011	135645	135	john brown	******				100.00			100.00	35.00	65.00	13.00	52.00		0.00	0.00	0.00	52.00
13	26/12/2011	134788	145	chris ali	******		100.00					100.00	35.00	65.00	13.00	52.00	50.00	5.00	9.00	36.00	88.00
14	26/12/2011	130256	145	chris ali	******			300.00				300.00	105.00	195.00	39.00	156.00		0.00	0.00	0.00	156.00
15	26/12/2011	130147	145	chris ali	******				75.00			75.00	26.25	48.75	9.75	39.00		0.00	0.00	0.00	39.00
16	26/12/2011	137474	145	chris ali	******					200.00		200.00	70.00	130.00	26.00	104.00		0.00	0.00	0.00	104.00
17												0.00	0.00	0.00	0.00	0.00		0.00	0.00	0.00	0.00
18												0.00	0.00	0.00	0.00	0.00		0.00	0.00	0.00	0.00
19												0.00	0.00	0.00	0.00	0.00		0.00	0.00	0.00	0.00
20												0.00	0.00	0.00	0.00	0.00		0.00	0.00	0.00	0.00
SERVICE MASTER

 

[Jonmo1]

Off Topic, but important...
In column E, those aren't credit card numbers are they?
Probably a good Idea to put bogus info there, or just blank them out entirely.
Keep personal info off the forum
They're not really relevant to the issue.


Back on topic, this is pretty complicated.
I get what you want now, but having a hard time figuring how to do it..

do you have another sheet with a unique list of patient names?

 

[proexecl]

it's okay there not a credit card numbers they just insurance policy member codes.

yeah i know it's very complected, and that's why i came to this site for
more professional help .

yes i do have with other names patient.

I have a workbook of more than 20 sheet with in each sheet have at list 669 rows.

i can sent it to you the actual excel work book if you want.

can you sent me your e-mail add in private message .

 

[Jonmo1]

Well, I wouldn't call this "professional"
Professional = Paid $$

We're all just regular folks spending a little free time trying to help.

My best suggestion would be
On the Sheet that has a unique list of patient names,
Create a running total of the deductible.

So say your unique list is on SheetX, in Column A
In say B2 put a formula to sum the deductible.

=SUMIF('SERVICE MASTER'!D:D,A2,'SERVICE MASTER'O:O)
And C2
=SUMIF('SERVICE MASTER'!D:D,A2,'SERVICE MASTER'S:S)
And D2
=B2+C2

That gives you a total of the deductibles on the Service Master sheet, for the person named in A2 on SheetX

Then you can start doing things like
=IF(VLOOKUP($D7,'SheetX'!A:D,4,0)>50,0,deductiblecalculation)


Just a general idea.


When you want to start talking about sending the sheet to me, that's getting professional.

MrExcel has a paid consulting service

You would very likely get a much better solution than i can provide anyway..


Hope that helps you out.