Nested If Function

[QLH]

Hello Everyone, I am trying to create a nested IF function in cell Y2 which will see if there is a positive value in cell AQ2 and return the contents of that cell if there is a value. If not then I would like to see if there is a value in cell AR2 and if there is then return the value of that cell. If neither of these are true then I would like to return the value of cell X2. Here is the formula I have been using =IF(AQ2>0,AQ2,IF(AR2>0,AR2,X2)). Unfortunately this will only read and return values in AQ and comes up blank when there is no value in AQ2. Another problem I think I'll have is that I will need the numbers to be rounded as they appear in the original cell (values in cell AQ have to be rounded to the hundredth and values in AR have to be rounded to the nearest whole number. Thanks in advance for your help.

 

[Special-K99]

Formula looks fine to me.
Must be a problem with your data, try posting a spreadsheet

 

[QLH]

Formula looks fine to me.
Must be a problem with your data, try posting a spreadsheet

Unfortunately, I can't post the spreadsheet (work rules) what kinds of things could I check for in the Data? Any Common troubleshooting tips? I have even tried copying text only into a different cell and then changing the cell references but that produced the same results.

 

[Special-K99]

Put 1 in AQ2
Put 2 in AR2
Put 3 in X2

Enter your formula in Y2
Given the above values the result should be 1
Now put 0 in AQ2
The result should now be 2 (since AQ2 fails the first test)
Now put 0 in AR2
The result should now be 3 (since AQ2 and AR2 fails the first test)

If you get these results then your formula works

 

[QLH]

I ran the test and the formula performed as it should. What could be wrong with the data?

 

[QLH]

Actually, I think I figured out what the data problem was. The formulas I has to calculate values in AQ and AR was set to return "" if false. I changed it to return 0 instead and the formula functions properly. However, I am still having the rounding problem. I need all the values in AR2 to always round up to the nearest whole number and the values in AQ2 to round up to the tenth. Any ideas on how I can achieve this?

 

[Special-K99]

What happens when it doesnt work as you expect?

Split the tests up

IF(AQ2>0,"Yes","No")
IF(AR2>0,"Yes","No") etc
to see where it may be failing

 

[Special-K99]

"" will return a cell as TEXT and consequently wont be greater than 0

 

[QLH]

Yes, I think that was the problem. Now I just need to figure out this rounding bit. The way it works is that it always has to round up. So, 28.4 still rounds to 29. Is that even possible to calculate via Excel?

 

[QLH]

OF Course! The ROUNDUP function. Ok I'm laughing at myself now. Thanks for all your help.