Two questions:
1) How do I make a cell (C1 for example) who's formula is "=A1-B1" show the difference of those two cells unless the answer is 0 in which case I want it to report "N/A"? In other words, if A1 is "10" and B1 "5", then C1 would report "5" but if A1 was "10" and B1 was "10" then C1 would report "N/A" and not "0."
2) This one is more involved as it relys on the value of another cell but here goes: I need a formula that will pull a number from a cell two rows above it but depending on the text in the cell two rows below it I may need it to adjust that number accordingly. For clarification: Assume that cell B5 will always contain 1 of the following 3 text entries: DISTINCT, CAPTURED, or ASSUMED. Cell B1 will always contain a number to one decimal place (i.e. 7.2 or 55.6). I need a formula in cell B3 that pulls the number from B1 but alters it depending on the text in B5. If B5's text is "DISTINCT" I want B3 to show B1's value exactly without any alteration. But if B5's text is "CAPTURED or ASSUMED" I want B3 to show B1's value exactly UNLESS B1 is less than 44 in which case I want B3 to display the "44.0." So if B1 is "98.0" and B5 is either "CAPTURED" or "ASSUMED" than B3 should report "98.0" but if B5 is "17.9" with B5 still being either "CAPTURED" or "ASSUMED" then B3 would report "40.0."
Any help would be greatly appreciated
Last edited by ZIXILPLIX; 05-01-2010 at 09:21 AM.
HI Z...
Answer to Q1: =IF(A1-B1>0,A1-B1,"#NA")
abousetta
Hi again Z...
Answer to Q2: =IF(B5="DISTINCT",B1,(IF(AND(OR(B5="CAPTURED",B5="ASSUMED"),B1>44),B1,44)))
Do you need this as a macro also?
Final point, just make sure that you format the cells as numbers with one decimal space.
Good luck.
Tell me if you need anything else.
abousetta
Hi Z,
Just in case the cells are not B, but are the same distance as your description, just use this simple macro and it will add the same formula to the active cell.
Sub Z() ActiveCell.FormulaR1C1 = _ "=IF(R[2]C=""DISTINCT"",R[-2]C,(IF(AND(OR(R[2]C=""CAPTURED"",R[2]C=""ASSUMED""),R[-2]C>44),R[-2]C,44)))" End Sub
Good luck,
abousetta
Thanks alot for the anwers abousetta!! Holy maceral!! You're a genius!!
Last edited by ZIXILPLIX; 05-01-2010 at 09:24 AM.
Deleted (is there an option to actually delete these posts?)
Last edited by ZIXILPLIX; 05-01-2010 at 09:25 AM.
Hi ZIXILPLIX,
Glad I could help. Anything from a friend from Tampa. Formulas are a really powerful way to use Excel. They are very flexible and allow you to an endless number of IFs.
Good luck,
abousetta
P.S. If you are satisfied withthe response, don't forget to mark the thread as [Solved].
I apologise for being remiss in not stopping this thread sooner.
Please take a few minutes to read the forum rules, and amending the thread title accordingly.
Thanks.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Thanks shg
Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel Tips & Solutions, free examples and tutorials why not check out my downloads
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
Deleted...
Last edited by ZIXILPLIX; 05-01-2010 at 09:26 AM.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
You can change the title at the same time
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel Tips & Solutions, free examples and tutorials why not check out my downloads
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
Hi Z,
The title should reflect the problem that you are facing (e.g. Combining mulitple IF statements in a formula) or something similar to this. Something that can be searched for in Google or a similar search engine.
As for marking the thread as solved, just go the first posting in the thread, click on EDIT then ADVANCED. Right next to the title you will find a drop-down menu with one of the options being [SOLVED]
Good luck,
abousetta
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks