# excel formula

1. ### Excel formula to get the result by few rules in 1 cell.

To seek help on excel formula to auto detect below 3 rules in 1 cell. Please advise. Rules: If decimal in cell more than 0.20 = 0.50; If decimal in cell more than 0.70 = 1; If decimal in cell less than 0.20 = 0. Amount (USD) Wanted Result (USD) 1,074.67 1,074.50 238.84 239.00...
2. ### How to output a dynamic range where the height of the range varies

Hi, I have a search tool that returns a dynamic range. I'm trying to get it so that the outputted dynamic range is staggered. I get this: <colgroup><col style="mso-width-source:userset;mso-width-alt:5266;width:108pt" width="144"> <col...
3. ### Excel Formula / Code

We have the following sheet Excel 2010 32 bit ABCDE 1 2 3 4 5S.No.YearBatch (Number of Students) 6Entry11993300 721932320 831978800 941924400 1051912200 1161993300 1271968800 1381994400 1491924400 15101983300 16111935500 17121956600 Sheet: Sheet1 We want a...
4. ### help with if statement

Hi all, I am having trouble with a nested if statement. I have several columns of data I need help with a formula that will look at the first column of data in column B if nothing found then go to Column C if nothing go to D if nothing go to E. If all the columns are blank then pull value from...
5. ### help to build a formula

Hello dear members of the forum. I'm trying to design a formula that allow me to simplify some calculations. I have 35 columns of data. In cell B90 I should execute this formula: <tbody> =sum(C44:C78)/sum(B44:B78) In cell C90 the following: <tbody> =sum(D44:D77)/sum(C44:C77) </tbody>...
6. ### Return value based on latest date for entry

Hi I have a spreadsheet with factory name, review date and overall evaluation. Some factories may have multiple entries and reviews. I would like the overall evaluation to return based on the latest review for each factory. Therefore if i have a duplicate factory on two review dates and the...
7. ### COUNTIFS with Date Criteria Help

I have a report I pull from one system into Excel format. I process it with some queries in Access and export it out to Excel. At no point do I actually edit the data in the field, but it is getting passed back and forth. The issue does happen in the source document, so the passing back and...
8. ### IF(ISNUMBER(SEARCH formula considering 2.5 and 5 as same. How to check for exact match?

Dear Sir, I have a formula based on which one excel cell changes value based on other cell value. The formula I am using...
9. ### MatchIndex for Multiple Criteria and with Duplicate Values

Hello Team! I am new to the forum though i use to regularly get help from the posts as a guest. I am hoping a question of mine would be solved:) I have a table with lot of data of opportunities in Pipeline from different departments in the organization, i want to get the Top 10 values based on...
10. ### offset formula question

Hi, I have a data set where the number of rows change when the data is refreshed. I am trying to count the number of rows in the population ( not including blanks).. I am tired of manually having to update the last cell of the range in the formula every time I refresh. I have dozens of...
11. ### Apply formula to cells without relative cell referencing in VBA

Hi, How do I prevent relative cell referencing with this formula? Dim LastRow As Long LastRow = Worksheets("data").Cells(Rows.Count, "I").End(xlUp).Row Range(Range("N2"), Range("N2").End(xlDown)).formula = _ "=SUMIFS(data!J2:J" &...
12. ### Matching Names with different criteria

I need help with a formula or set of formulas to match 2 columns of names. One column will have first and last name and the second column will have last comma first name and sometimes a middle initial. How can I compare the 2 to match the data sets without the middle names, suffixes, etc. I...
13. ### A shorter alternative to IF formula

Is there a shorter way of doing this? please. =IF(AND(A1>0,A2>0),1,0)+IF(AND(B1>0,B2>0),1,0)+IF(AND(C1>0,C2>0),1,0) Basically, if A1 and A2 are greater than 0 at the same time, it should be counted as "1", if not 0, and then all results in the range are summed. My data are like this...
14. ### Excel Formula Help - Multiple IF/AND Statement with Dates

Hello All, I am in need of some help with a hard IF/AND Statement with multiple variables My current Formula: =IF(T\$4=\$J9-1,\$Q9*0.3,IF(T\$4=\$J9,\$Q9*0.5,IF(T\$4=\$J9+1,\$Q9*0.1,""))) Definitions T\$4 = is my header date (Formula is dragged left to right in the same row \$J9 = My Starting Date...
15. ### What is wrong with my MATCH formula?

Hi guys My simple MATCH formula is not giving the right answer. Can you help find it? In row 1, I have the following: <tbody> Q3 F2019 Q3 F2019 Q3 F2019 Q4 F2019 Q4 F2019 Q4 F2019 Q1 F2020 Q1 F2020 Q1 F2020 Q2 F2020 Q2 F2020 Q2 F2020 Q3 F2020 Q3 F2020 Q3 F2020...
16. ### How to pull data for quarterly and annually information?

Hi friends I am struggling to build a dashboard which shows quarterly and annual results side by side. I have my detailed sales information in another sheet like this: Column A Column B to Column AZ # of Referrals Month wise data (i.e. the # of referrals...
17. ### Hyperlink with vlookup formula not working

Okay I give up I have tried this multiple ways and both ways my formula shows up correctly however its not taking me to the cell I am requesting. I am really new at excel so I apologize if I don't make sense thank you in advance I really appreciate the help...
18. ### Multiple conditional formatting to the same range

Dear, Please help me with the following situation. I need to have the following two conditional formatting to the same range W4 to W1004: =IF(IF(AND(\$Q4=""),(\$W4=""),IF(AND(\$Q4="N/A"),(\$W4=""),IF(AND(\$Q4<>""),(\$W4<>"")))),"",IF(AND(\$Q4<>""),(\$W4="")))...
19. ### Combining a sumifs and round in one statement

Hi, I am trying to combine round and sumifs in one statement but its not liking what I am doing . Is there an easy way to do this? =ROUND(SUMIFS('2.3 Lines'!\$T:\$T,'2.3 Lines'!\$F:\$F,"Service"),0) Thanks, Sharon
20. ### If, AND, OR in the same formula

Hi there, I am trying to create a formula that if the criteria in column D = Z and either the value (OR) in E2 or F2 is blank to return the value "Check" =IF(AND(D2="Z",OR(ISBLANK(E2)="True",ISBLANK(F2)="True")),"Check","Fine") My formula does not evaluate correctly above - any ideas?