excel formula

  1. L

    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. D

    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. V

    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. A

    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. R

    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. T

    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. B

    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. P

    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. T

    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. A

    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. G

    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. K

    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. U

    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. Johnny Thunder

    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. I

    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. I

    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. S

    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. D

    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. A

    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. M

    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?

Some videos you may like

This Week's Hot Topics

Top