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]

Welcome to the board..

Try the MIN function..

=MIN(50, calculation)

It will return whichever number is smaller (Minimum)
So if calculation excedes 50, then the function returns 50
But If calculation is less than 50, then it returns the result of the calculation.

Hope that helps.

 

[proexecl]

Dear jonmo1

thank you for your support but unfortunately that dose not answer or solve my question, I have tried the =MIN function before it work only for one cell but in my case i have four (4) deductibles, i have to add each deductibles than if they total rich more than 50$ i want to stop.

i want to know if you have seen the picture of the sheet ?,

any further idea will be very helpful (jonmo1)

waiting to receive any new thought's

 

[Jonmo1]

yeah, the picture doesn't really help though.
To post a helpful display of your layout, try using the Excel Jeanie
See my signature for a link.


Can you post the actual complete formula that works (except limiting to 50).
And describe which part(s) of that formula you want limited to 50.

 

[proexecl]

I have visited the website, in order to give a better look at the sheet,
but there is no place to put my file nor the picture (screen shoot). i even click on the forum which is not yet available. i even look for e-mail add to send you the actual file (workbook).

Dear jonmo1 for you to understand my needs very well, why don't you give me your e-mail add than i can sent it to you. only if that's okay with you .

 

[Jonmo1]

After you install the Excel Jeanie..
In Excel, go to tools - addins and select Ejhtml4_En
Choose whatever default setting it presents you with.
then you highlight a range on your sheet that you want to display here.
And click the button for Excel Jeanie
It presents a window - accept all defaults.
And click the button that says Forum Standard.
Then a Browser windows appears showing a preview of what it would look like on the forum.
the HTML code is automatically put in your clipboard.
Simply paste it into the forum (it will look like jibberish cause it's HTML code)
Then submit.

here's a small example of what it will do.

Excel Workbook
	A	B	C
1	Header1	Header2	Header3
2	5	7	9
3	4	6	8
Sheet1

 

[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	2267477012		75.00					75.00	26.25		9.75	39.00	50.00	5.00	9.00	36.00	75.00
8	25/12/2011	142314	314	john smith	2267477012			200.00				200.00	70.00		26.00	104.00		0.00	0.00	0.00	104.00
9	25/12/2011	142315	314	john smith	2267477012				250.00			250.00	87.50		32.50	130.00		0.00	0.00	0.00	130.00
10																		0.00	0.00	0.00	0.00
11																		0.00	0.00	0.00	0.00
AL MAJAL SERVICE MASTER

 

[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	2267477012		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	2267477012			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	2267477012				250.00			250.00	87.50	162.50	32.50	130.00		0.00	0.00	0.00	130.00
10																		0.00	0.00	0.00	0.00
11																		0.00	0.00	0.00	0.00
AL MAJAL SERVICE MASTER

 

[Jonmo1]

Great, now which formula(s) or which part of which formula do you want limited to 50?
You can now just give a cell reference to tell which formula(s)

 

[proexecl]

Thank God, we understood each other, as you can see i have highlighted with red (the deductible ) I want those deductible to be maximum 50$ if it rich higher i want it to stop and put the remaining amount the net.

dear jonmo1, the idea is, let say you have an insurance card deductible of 20% up to 50$ and you take service at any clinic, let say the clinic give 50 % discount to the insurance company and 10% discount in medicine. the clinic will collect the fund from the insurance company also up to 50 $ from thee card holder (you) but if the deductible rich more than 50$ the clinic going to ask the extra from the insurance company since the card holder maximum is 50 $ .


hope you understand me this time