How do I combine Excel formulas?

by on December 1, 2011

Q: I am trying to answer a formula for an excell spreed sheet. I am trying to make a cell know two things… here is my formula for one area and the formula for another area, but I can’t make the cells understand I want to know what’s between the two formula when a certain letter is entered into one cell and a # is entered into another..

=IF(AND( H3=”W”, M3*(COUNTIF(M3,”<$10,000″))),M3,””),
=IF(AND( H3=”L”, M3*(COUNTIF(M3,”<$10,000″))),M3,””)
=IF(AND( H3=”P”, M3*(COUNTIF(M3,”<$10,000″))),M3,””)

is one area and then I have in another area:

=IF(AND( H3=”W”, M3*(COUNTIF(M3,”>$100,000″))),M3,””)
=IF(AND( H3=”L”, M3*(COUNTIF(M3,”>$100,000″))),M3,””)
=IF(AND( H3=”P”, M3*(COUNTIF(M3,”>$100,000″))),M3,””)

I am trying to combine the two types of formula together. I want the cell to know when I have a number between $10K and $100K and whether or not its a P, W, L…. Pending, Win, Loss.

all of the attempts to input the two formula’s together I receive an error… I am racking my brain over this.. I am hoping that I am giving you enough information, but I am sure I am leaving something out.

Example:

Cell:
H3 M3
Pending Won Loss

W $11,000.00 $0.00 $11,000.00 $0.00
P $50,000.00 $50,000.00 $0.00 $0.00
L $88,000.00 $0.00 $0.00 $88,000.00

But if it is more than 100K or less than 10K it shouldn’t appear in any of these cells…

W $110,000.00 $0.00 $0.00 $0.00
P $500,000.00 $0.00 $0.00 $0.00
L $8,000.00 $0.00 $0.00 $0.00

I have the formula sending them to different area’s should they exceed 100K or don’t exceed 10K but for the life of me I can’t make them understand I want the inbetween to go into these cells.. Is there anything you can do to help me?

brandiworster@adamstank.com
for more information…. Thank you for your time in advance…

I have run out of resources and people to ask, and I am hoping you can assist me with the complicated problem. Thank you!