• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Capturing figures automatically with help of formulas

Gaurang Mhatre

Gaurang Mhatre

Member
Hi Friends,

I am maintaining one excel where there is lot of manual working.

I want a formula which will capture it automatically since I am unable to do so.
Details I have mentioned in excel sheet.


Please help me once you get time.


Thanks for your help as always.


Regards,
Gaurang.
 

Attachments

Hi Gaurang ,

Try this :

=COUNTIFS(INDIRECT("Volume" & "!" & B$2 & 3 & ":" & B$2 & 260),">50",Volume!$I$3:$I$260,$A16)

There is a problem regarding FIPS , which in your Volume tab is two categories , FI and PS.

Narayan
 
Hi Gaurang ,

This is quite complicated ; I have just listed all the figures for each quarter and client ; see if they are OK , and then we can see how to get the top 5 from this data.

Narayan
 

Attachments

Thanks a lot padawan...
Actually the formula is too long and difficult to understand. Can you explain a bit.?? Logic behind it.

Thanks once again for taking out your time.

Regards,
Gaurang
 
Gaurang,
The 1 thru 5 in Col O are used in conjunction with the LARGE function to get the
Largest SUM from Col N, the 2nd largest SUM, the 3rd largest SUM, etc.

MATCHing the 5 highest values in Col P with the SUMs in Col N tells us which row each came from, so we want the Clients from those same rows. I nested the MATCH function inside the INDEX function to give me the row_num parameter value.

Let me know if you need further clarification and I'll be glad to do that.
 
Gaurang Mhatre said:
Thanks a lot padawan...
Actually the formula is too long and difficult to understand. Can you explain a bit.?? Logic behind it.

Thanks once again for taking out your time.

Regards,
Gaurang
Click to expand...
Gaurang, one other thing while I'm thinking of it. Nested formulas can be difficult to understand and even more so to troubleshoot. The Evaluate Formula dialogue box is one of my best friends and should become one of yours. For instructions on where to find it and how to use it, try:
http://office.microsoft.com/en-us/e...d-formula-one-step-at-a-time-HP010066254.aspx
I'm not crazy about contrived examples, so paste this formula into cell A1 and perform an Evaluate Formula on it. To see the formula's usefulness, extend it to the right. It'll work all the way to column XFD. Comes in handy when using the INDIRECT function.
 
Last edited:
You must log in or register to reply here.
Back
Top