Could someone please help me with formulas

  • Thread starter Thread starter No
  • Start date Start date
N

No

Because it isn't right to have attachments with posts I have created a
webpage to show what formulas I need help with.
The link is
http://www.eaglepi.com/formula/index.html

Everything should be clear as to what I need, I have given examples to help
you understand what I am trying to do.

Thank you in advance
 
This might suffice ..

Put in H2:
=IF(E2="Yes",5,IF(F2="Yes",10,IF(G2="Yes",20,"")))

Copy H2 down
 
Maybe this is what you're looking for...........
=IF(AND(E2="yes",F2="no",G2="no"),5,IF(AND(E2="no",F2="yes",G2="no"),10,IF(A
ND(E2="no",F2="no",G2="yes"),20,"")))

It's all one string, watch out for wordwrap in the email..........
Besides the "yes" in the appropriate cell, it also requires the "no's" in
the other cells............

Vaya con Dios,
Chuck, CABGx3
 
Sorry, I read too fast and only gave you the formula for
"H".............here's the one for "I"

=IF(H2=5,(C2-H2)*0.2,IF(H2=10,(C2-H2)*0.3,IF(H2=20,(C2-H2)*0.5,"")))

Vaya con Dios,
Chuck, CABGx3
 
Max,

Your forumla works for the Co-pays" H " but it doesn't do anything for
"I " the total

but thanks for trying to help
 
Your forumla works for the Co-pays" H "
but it doesn't do anything for "I " the total ..
Click to expand...

Sorry, must have missed out completely the part on col I in the 1st attack
(my bat radar wasn't working too well <g>)

Try in I2:

=IF(E2="Yes",(C2-H2)*20%,IF(F2="Yes",(C2-H2)*30%,IF(G2="Yes",(C2-H2)*50%,"")
))

Copy I2 down
 
There is nothing wrong with the answers that you have been given, so just
for the exercise:


=IF(COUNTIF(E2:G2,"Yes")>0,SUM((H2={5,10,20})*((C2-H2)*{0.2,0.3,0.5})),"")

Regards

Sandy
 
So far nobody has given me a formula for " I " that works, some work but the
calculations are wrong..

I'm still trying to figure it out and welcome any help.
 
Both of the formulas offered for "H" and all three of the formulas offered
for "I" seem to work for me on XL2k, and according to my understanding of
what you want...........

Could be you're getting caught with the "word wrap" that sometimes occurs
when emailing long formulas..........make sure you're getting the complete
formulas in the cells..........

If you want more help, please give an example of exactly which formula does
not give you what you want.....and be as specific in describing the
deviation as you can..........tks

Vaya con Dios,
Chuck, CABGx3
 
I think there are better ways, but here is one quick and dirty option.

H1 ->
=SUMPRODUCT(--(E1:G1="yes"),{5,10,20})

I1->
=(C1-SUMPRODUCT( --(E1:G1="yes"),{5,10,20})) *
SUMPRODUCT( --(E1:G1="yes"),{0.2,0.3,0.5})


Perhaps use Data Validation to make sure you have at most one "yes" in
E1:G1.
HTH
 
OK thanks for your help, here is what I want to happen.

in " D " start with a price of $107.75 "E " should be "NO" " F " should be
"yes" " G " should be "no" " H " should be $10.00

example: if in "D" the amount is $107.75 and in "e" is "no" "f' is "yes"
'g' is "no" " h " would have $10.00, " i " would be total of $107.75 minus
(H)$10=$97.75 X 30% = $29.33 Now I'll explain what this is, "D" is the
cost of a prescription drug

"E" Generic ? yes or no

"F" Brand Name ? yes or no

"G" Non-Brand Name ? yes or no

"H" is the co-pay,

if it's generic co-pay =$5 plus 20% of balance

if it's Brand Name =$10 plus 30% of balance

if it's Non-Brand Name =$20 plus 50% of balance

"I" would be the total after taking the Cost $107.75, because it's a brand
name drug the co-pay would be $10 now you would take $107.75 minus$10=$97.75
X 30%= $29.33 So the total in I for this example should be $29.33



Hope that makes it easier to understand
 
No said:
OK thanks for your help, here is what I want to happen.

in " D " start with a price of $107.75 "E " should be "NO" " F " should be
"yes" " G " should be "no" " H " should be $10.00

example: if in "D" the amount is $107.75 and in "e" is "no" "f' is "yes"
'g' is "no" " h " would have $10.00, " i " would be total of $107.75 minus
(H)$10=$97.75 X 30% = $29.33 Now I'll explain what this is, "D" is the
cost of a prescription drug

"E" Generic ? yes or no

"F" Brand Name ? yes or no

"G" Non-Brand Name ? yes or no

"H" is the co-pay,

if it's generic co-pay =$5 plus 20% of balance

if it's Brand Name =$10 plus 30% of balance

if it's Non-Brand Name =$20 plus 50% of balance

"I" would be the total after taking the Cost $107.75, because it's a brand
name drug the co-pay would be $10 now you would take $107.75 minus$10=$97.75
X 30%= $29.33 So the total in I for this example should be $29.33



Hope that makes it easier to understand
Click to expand...

Other than the fact that you seem to have changed the reference from "C" in
your web page to "D" now, all the formulas that you have been given will
return the answer that you are looking for.

Regards

Sandy
 
Maybe in cell J2 you could put the formula =H2+I2, which would give you the
sum of the Co-pay(H2), and the percentage of the balance
(I2).................or, this could be incorporated into the I2 formula by
adding +H2 to the end of it...........

If that don't get it, then please tell us exactly "how" the formulas are not
working for you.........you've told us how you want the cells to be, and we
believe we've given you formulas to do that, but you say they are not giving
the correct results, so in order for us to help, you will have to describe
exactly what is going wrong.......maybe put up another web page with the
formulas we've given you in place and a description of what's not
working..........or, you can send me your file direct to my home addy and I
will take a look for you

Vaya con Dios,
Chuck, CABGx3
 
Sandy Mann said:
Other than the fact that you seem to have changed
the reference from "C" in your web page to "D" now, ....
Click to expand...

Ah, guess my bat radar works ok afterall <g>
 
Back
Top