LinkBack URL
About LinkBacks
Hello,
I have this formula but am not getting the intended results, can anyone point out if anything's wrong with it?
=IF(year=0,0,IF(year=1,rent_year_1,IF(AND(year=2,year=3),rent_year_2_and_3,IF(AND(year=3,year=4),rent_year_4_and_5,0))))
Thanks
Hello,
I have this formula but am not getting the intended results, can anyone point out if anything's wrong with it?
=IF(year=0,0,IF(year=1,rent_year_1,IF(AND(year=2,year=3),rent_year_2_and_3,IF(AND(year=3,year=4),rent_year_4_and_5,0))))
Thanks
Are year, rent_year_1, etc named ranges?
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
no, they're just a single number which should be anchored with dollar signs if i am not using the define name feature! hmmm
Then you cannot specify it as year and rent_year_1. You will need to put in the cell references.
Does that mean only ranges can and should be defined by names?
There is still something wrong with this formula even when i write it this way regardless of name definitions. what is it ?
=IF(year=0,0,IF(year=1,200,IF(AND(year=2,year=3),400,IF(AND(year=3,year=4),800,0))))
Can you upload a sample worksheet for this?
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Okay, I assume "year" is a named cell? For example if you have the year number (1,2,3, or 4) in cell A1, you can go to the name box (where you see "A1" in the white box upper left) and type in year then ENTER. From then on, cell A1 is known as year.
You can't have year = 2 and 3 or 3 and 4. It has to be one or the other. Can you upload a small example of how your data is set up showing what cells you are looking in? (Go Advanced> Manage Attachments).
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Thanks all. attached is the spreadsheet:
(1) I need to fix the formula
(2) I want to define all call ranges and references with names. I know i can define ranges but i can also define names for cell references to make formulas in complex sheets easier to read? (this is related to my last recent post as well)
Thank you very much!
First you need to change the IF(AND parts of your formula to IF(OR
Ah yes! That 's right how stupid of me! Thanks Fotis
what about definitions can i define cell references?
You can use Named cells but i don't see why this will be useful for you...
A few things.
1. you have year 3 = both 200 and 300. The if statement will always do the outside nested IF first so year 3 will always result in a 200.
2. you have nothing for year = 5, therefore it returns FALSE.
2. You could define the entire range G9:V9 as year. Select G9:V9 and Formula tab>define name
Then if you refer to Year, it will pull the value of year from the same column. For example, in your example, in H20, year will equal 1, in U20, it will equal 5
This works for you because your formulas are right below your years (same columns). If you refer to a year in a different column, it'll screw up.
Make sense?
Another point, Year is an Excel Function. Best not to name variables the same as functions (I'm surprised it lets you). I'd make it Yr or Years or even _year but that's just me.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks