Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have to derive a formula for the following conditions:

  1. If company is listed in ROC then the result should be Applicable (for XBRL)
  2. If company is unlisted then check the following conditions:
    • If Paid up capital is >= 50,000,000 OR Turnover is >= 1,000,000,000 then result should be Applicable
  3. Else the result should be NA
share|improve this question
1  
Hi Ankur! Could you maybe give us a brief description of what you've already tried and where exactly you're stuck in that process? I'm sure you've come up with some formula already, or have some sample data to give us a better example of what you want to achieve. –  slhck Oct 22 '12 at 7:59
add comment

2 Answers

up vote 1 down vote accepted

See @AdamV's answer. It's straightforward for this question.

Assuming L1 says if the company is listed or not (with a "YES" or "NO"), C1 contains the paid up capital and T1 contains the turnover, you could do it this way:
=IF(L1 = "YES", "Applicable", IF( OR(C1 >= 50000000, T1 >= 1000000000), "Applicable", "NA"))

Notes:
* Q & A: Things you really want to know about Excel IF function
* For future reference: nested IF functions and OR function
* Read up on using Excel functions.

share|improve this answer
    
You don't need nesting here. It looks that way from the way the question is framed, but it's actually a straight OR if you drill it down. Useful links though get you a +1 –  AdamV Oct 22 '12 at 8:31
    
You're right, @AdamV. –  M K Oct 22 '12 at 8:34
    
ya... i m going through nesting.... but it is as simple that i never think abt it.... thanks one again @AdamV... thanks a ton.. –  Ankur Rohilla Oct 22 '12 at 9:39
add comment

sounds like a job for an OR.

Assuming:

  • Column A is company name
  • Column B is Yes/No (or Yes/Blank) for "is listed in ROC"
  • Column C is Paid up capital amount
  • Column D is Turnover

(these could be anywhere of course, just stating some model assumptions so the following formula makes sense). In cell E2 you could use something like:

=IF(OR($B2="Yes",$C2>=50000000,$D2>=1000000000),"Applicable","NA")

This checks if a company is listed or has paid capital more than your figure or turnover more than your figure. It does not need to do this in two steps, as if a company is listed and has capital/turnover greater or less than those figures, it is "Applicable". If it is not listed but wither figure is greater than you asked for, it is applicable. No need for AND/OR or any nested IFs.

If you don't already have a column for "is listed in ROC" then can you tell us more about how you would expect to determine this - do you have some other column that has a list of comanies that column A can be checked against, for example?

share|improve this answer
    
To @Ankur, this is the better (and simpler) solution. –  M K Oct 22 '12 at 8:35
    
Thanks AdamV...... its great, i was using And & or function to derive but not able to.. –  Ankur Rohilla Oct 22 '12 at 9:35
    
Glad it works - feel free to accept this as an answer... –  AdamV Oct 22 '12 at 12:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.