Search results

Hi All, I am trying to find a formula and breaking my head for quite some time. I will receive data as in the attached file. Unfortunately, I should not change the data format as it is used for further processing down the line. i need total sub category based on product name. the data...

hi, why not use Vlookup() formula? Regards, Prasad DN

Hi, I hope the above solution provides you what you wanted. In case you wish to get the same when dates are not sorted, or by not entering the first date manually, here is the formula, non CSE: if Dates are in row: Define Name: Range1 = Transpose($H6:$BY$6) if Dates are in Column: Define...

Hi, If you put correct input for in Find and Replace, it would work perfectly. Note: ",0)" is changed to ",2)". regards, PrasadDN

Hi Megan, Welcome to Forum! In case you still want to merge your long list, you can: 1. Select your list + number column you to merge them. 2. click the small arrow besides Merge & Center, which gets you drop downlist. 3. click Merge Across 4. Say Loudly "I am happy, its done" Regards...

Hi, The file not only gives you unique values but the your initial requirement of consecutive pay periods, (small, k). Even with your new row wise data, you can use transpose() for Range1, and the result. Coming to your specific countif formula, the below formula works, but with one...

Hi, Define Name: Range1 as =Sheet1!$D$5:$D$74 You need to apply this formula for E2: =SUMPRODUCT((Range1<>"")/COUNTIF(Range1,Range1)) This will fetch you number of unique values. And the below CSE formula can be pasted anywhere in the and dragged down to get the unique list of dates...

I got your point what FIFO is, but what I really did not understand, I am sorry, is which column/row you need to calculate and what is the logic. Pls try explaining with few examples. regards, prasad DN

Hi, Here is what best i could get: = SUM(IFERROR(((A16=EOMONTH($A$8,COLUMN(INDEX($1:$1,1):INDEX($1:$1,COLUMNS($A:U)))))/1)*($D$8:$U$8),0)*$B$8)+ SUM(IFERROR(((A16=EOMONTH($A$9,COLUMN(INDEX($1:$1,1):INDEX($1:$1,COLUMNS($A:U)))))/1)*($D$9:$U$9),0)*$B$9)+...

Hi David, I was just wondering where would you apply such formula? or how this is going to help you in larger picture. If it is not very confidentially pls throw some light on where you come across such need. Regards, Prasad

I was not able to understand the meaning and function of FREQ and did not make any sense of what is data and bins. I did some search and now it is very clear about what this function does, and returns. I referred the site: http://exceluser.com/formulas/frequency-distribution-five-ways.htm...

Hi, Well, for my question above in post #7, I figured out the formula myself: =SUM(IF(MMULT(IF((C3:E11>=F3:F11),1,0),{1;1;1})>=1,1,0)) enter as CSE. Regards, Prasad DN PS: Incase anyone wanted to know.

Hi, Also you may be interested to tweak a bit in formula to drop the first char of string only if it *, because I see few cases where the one letter of the name is missing. regards, Prasad DN

Hi, Also pls clarify what si the logic of extracting % from table1? Regards Prasad DN

Hi, on a side note, if I need to know how many countries did reach goal atleast once in those 3 months period, how would i acheive? If a country acheives more than once goal in those three months period, it should be still counted as one country. Since we have 9 countries listed here, our...

Hi, Pls also provide how is fifo q and v calculated. and also few examples values manually entered. Regards, prasad

why not you give a shot yourself?

I guess its because you are not entering the formula as CSE. CSE, means you will type or paste the above formula in a cell and instead of you hit enter or return key, you will hit Cntrl+Shift+Enter key together. Regards, Prasad DN PS: if you enter as CSE, you will see the formula gets { and...

Hi, use below formula as CSE: =MIN(IF((A2:A16=A19)*(B2:B16=A20)*(C2:C16<=A21),E2:E16,"")) regards, Prasad DN

Hi Again, I did go thru the link above, and first thing first, awesome site for learning advanced formulas!! I will be a regular visitor of your site from now. Coming back to this thread, I failed to understand the Frequency(), i am not getting what is this function will return and what...

Hi, Pls post a sample file with sample data. Regards, Prasad DN

Hi, I think you are looking for formula to find rows that are bold (dynamically), and return those rows. AFAIK, there is no formula to find if the cell is bold or not. And, unless you figure out this, you cannot return those rows. Now, you have the solution in hand, by letting us know, on...

Hi, Alternatively, you can use vlookup() and to supress #N/A pass the result with IFerror(). example: =IFERROR(VLOOKUP(A7,'2013holiday'!A2:C126,3,0),"") Regards, Prasad DN

Hi, Use this formula: =IF(D13>5,((5*(E13/2))+(D13-5)*E13),(D13*E13)) Attached is the file. Regards, Prasad DN:

Hi, My mistake.... :( I did not notice the braces ( ) . It is indeed giving correct result "A" now. I am really sorry for my above post. BTW, yes it was showing correct results without 1+ as well. Regards, Prasad DN