How to calculate multiple strings in same cell

[docassist]

String Multiples for 3 different criteria in same cell -- "if", "or" "abs" --- not sure if any of these are correct
1) Criteria 1 and 2: In cell "E" I am wanting to minus "B from C", if "A" is greater than or equal to and less than "=>1000 or <15000"
2) and the other criteria would be if "A" is greater than or equal to ">=15000" then minus "b from d" -- answer would also be in the same cell "e".

I hope I am saying my needs correctly. Basically, I have 3 different sales criteria with 3 different bonus offers. One offer is for sales less than 10,000, another offer is for the range 10,000 to 14,999 and the last is for the range 15,000 or more. I am actually linking the values from different worksheets, however I have used generically "A, B, C, D and E".

below are the 3 separate formulas that do work on specific individual rows. However, I really need the criteria for all three variables in the same cell. Right now I am just copy/paste the appropriate formula that fits the specific value in each row. Not good, I know. :-(
=IF(summary!$E8>15000,SUM(F3-summary!D2),0)

=IF($E4<15000,ABS(F4-summary!D3),IF($E4>=10000,0,(F4-summary!D3)))

=IF($E14<15000,ABS(F14-summary!D13),IF($E14>=10000,0,(F14-summary!D14)))

[Richard Buttrey]

Hi,

Not quite sure if I've understood your requirement, if not upload an example workbook with some sample data and manually calculated results indicating which are the results.

In general though

Please Login or Register  to view this content.

just substitute the calculations that you want to apply for the three literal results.

[rcm]

what is your criteria to copy/paste a formula ?

[docassist]

Basically, I am trying to calculate a CR (reduction) off Base Rent that is based on the Sales Price (Net Rent).
Argument 1: If the Sales Price is 15000 or greater, then the SBCR is 199. Therefore, with a Base Rent of 499 the CR is 300.
Argument 2: If the Sales Price is from 10000 to 14999 then the SBCR is 299. Therefore, with Base Rent of 499 the CR is 200.
Argument 3: If the Sales Price is less than 10000 (0 to 9,999) the SBCR is 0 (Zero). Therefore, if the Base Rent is 499 the CR is 0 or blank.

The sample below are cells pulled from my summary worksheet:
SBCR-1 199
SBCR-2 299

The sample below are cells from my Data worksheet:
Sales Price Base Rent Credit
15,900 499 300 =IF(summary!$E8>15000,SUM(F3-summary!D2),0)
13,900 499 200 =IF($E4<15000,ABS(F4-summary!D3),IF($E4>=10000,0,(F4-summary!D3)))
9,900 499 0 =IF($E14<15000,0,IF($E14>=10000,0))

When I said that I copy/pasted I meant that when I visually saw the Sales Price, I would paste a correct formula for that Row. I would like to have the 3 arguments for every row that covers the 3 different Sales Price Ranges. I have such a huge margin of error using only my visual of each row.

Richard, I will try your formula and let you know if that works. I appreciate the time and suggestion to my problem.

[docassist]

Thank you so much!!!! The link you suggested to me gave me many scenarios. I was able to find a suggestion to help me move forward. Just in case you would like to know, here is the formula I ended up using AND IT IS PERFECT!!!!

=IF(E15>15000,F15-summary!$D$2,IF(AND(E15>=10000,E15<=14999),F15-summary!$D$3,IF(AND(E15<=9999),0)))

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

[docassist]

Thank you, Administrator. I was so excited that the formula worked that I didn't look to see that "Solved" was my next step. However, I think that I did press "Star" for reputation. I will try that one again.

This was my first post. Everyone was awesome. Perhaps I can return the favor someday.