### Excel SUMPRODUCT “greater if” #VALUE! error

I’m having an issue when using the SUMPRODUCT function to reference tables with “greater than” and “less than” in SUMIFS values. More specifically, the following gives me a “#VALUE!” error. =SUMPRODUCT(SUMIFS(Perf_Data[Gross Claims Paid (non-cumulative)],Perf_Data[Year (AY/UWY)],”<“&’OUTPUT – S.19.01′!$A24,Perf_Data[Development Year],”>=”&Perf_Data[Year (AY/UWY)]+15,Perf_Data[Applicable Line of Business],LoB_Selection),Perf_Data[@[Currency check and multiplier]]) The code above sums the gross claims paid, if their […]

### Vlookup 2 column index numbers

I need to look in 2 index value, if there is no value present in that index, it should check the next index number and pop the value. I tried these 2 formulas, its not working : =IFERROR(VLOOKUP(A5,’sheet1′!A:S,**19:20**,FALSE),””) =IF(NOT(ISNA(VLOOKUP($A5,’Sheet1′!A1:T92,19,0))),”Main”, IF(NOT(ISNA(VLOOKUP(A5,’Sheet1′!A1:T92,20,0))),”Residual”,”Notfound”)) Here i need to pop up S, in case if column S is blank take […]

### SUMIF for a wider range

I have a problem that has been nagging me for a while now. I am a novice in Excel. Hence excuse me if this comes across as silly. I have the following array of data: I need to obtain the sum of values corresponding to the row entry “book”. I have used the formula sumif(B4:F7,”book”,C4:F7). […]

### Get the Last String value of n length after a delimiter from string using excel formula

I need to get the last split value from the cell. The input cell C1 value = main\java\com\tms\vsc\ppo\data\domain\Ppo.java which must be copied to another cell B1 Expected output in B1 is Ppo.java Below is the formula tried over in cell B1 =RIGHT(RIGHT(C1,FIND(“\”,C1)),256) and =RIGHT(C1,FIND(“\”,C1)) Can anyone help me out. Its a pure excel and not […]

### Table of name ranges in Excel

I have a column called Mode having different values (Air, Surface etc.) In the below formula I am using Named Range with the same name as Mode (Air, Air_r, Air_c, Surface, Surface_c,etc.). =IF([@Mode]=”AIR”,CONCATENATE(LEFT(RIGHT(CELL(“address”,INDEX(AIR,MATCH(IF([@[Charged Weight]]>0.5,0.51,[@[Charged Weight]]),air_r,1),MATCH([@Zone],air_c,0),1)),3),1),RIGHT(CELL(“address”,INDEX(AIR,MATCH(IF([@[Charged Weight]]>0.5,0.51,[@[Charged Weight]]),air_r,0),MATCH([@Zone],air_c,0),1)),1)),0) How can the Index Array & rows & columns be changed dynamically with reference to value in Mode […]

### Formula to check if one cell is within a range between two cells from a list

Say I have the value in D2. I want to see if that value is within the ranges B2-C2, B3-C3, B4-C4 and B5-C5. If it is, I want to find a function that returns the text from A2:A5 where the value fits within the ranges. I would then use these values in a future function. […]

### Get the reverse formula for another column based on existing column's formula in excel

I have a list of base price but donâ€™t have the list price. Below are the formulas under column W and column AN. Formulas: W: =ROUND(T3+(ROUND(T3*V3,2)),2) AN: =ROUND(W3-(ROUND( (ROUND(W3*Y3,2)) + (ROUND(W3*AD3,2)),2)),2) In order to get the list price, I need to use trial and error method to find out the list price for each items […]

### Compare the value with the next 5 rows value if there is a 10% increase

EXCEL how to compare the value and next 5 rows value if there is a 10% increase. something like that if (A1 > A2*1.1 or A1 > A3*1.1…or A1 > A5*1.1) then return true; A B C D E 1 1 2 1.1 3 0.9 4 0.9 5 0.9 6 0.9 7 0.9 H 0.9

### Trouble with Conditional Formatting Date + Other Conditions

I’m relatively new to Excel and have to stitch together something. I’m having some trouble with conditional formatting. What I’d like to do is, for each line, type in the date of request. The hidden column is just the date of request +21 days. For the heading records rec’d, I have it elsewhere with 1 […]

### Increment with text value

I am making a log file in Excel for some laboratory samples. In this log I record the date, the sample ID, the box where the sample is stored, and the box-position. For example: SampleID Study Date Box Position 12345 SomeStudy date 1 A3 12346 SomeStudy date 1 A4 The positions always start from position […]

Interesting Posts