Posts by Seti

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • Why it doesn't work for 5 American Dollars

    Re: Why it doesn't work for 5 American Dollars


    I think that the problem is the way you have created the nested IFs. If the value in R11 is greater than 12, only the first IF is executed, so you would get R11-12, even if R11 > 36, it will not get to that portion of the nested IF statement. Once the first TRUE condition is encountered, it will not keep processing. Maybe try to change the order around to check for the greatest value first. Maybe the MOD function proposed does what you want. You could also make it look a lot more complicated with AND conditions like IF(AND(R11>12, R11<24),R-11...

  • Vlookup needs revision

    Re: Vlookup needs revision


    OK, here are the formulas that should work:


    In J6 put =VLOOKUP(I6,Budget!$C$2:$EO$7493,44,1) and copy down
    In K6 put =VLOOKUP(C6,Budget!$C$2:$G$7494,5,1) and copy down
    in M6 put =VLOOKUP(C6,Budget!$C$2:$Z$7494,24,1) and copy down


    The problem with column J is that you were looking at column A on the Budget sheet rather than column C, so I changed this and shifted the lookup up column two columns back.


    Also in all three formulas, you used relative references rather than absolute references. That is what the $ does in the cell range. Hope this helps some.

  • Macro (orother method) to identify duplicates considering 2 columns

    Re: Macro (orother method) to identify duplicates considering 2 columns


    You could probably do this with conditional formatting as well. Let's assume that you have the data in columns X and Y and that they start in row 3. Create a new rule for cell X3 using a formula and use this as the formula: =COUNTIFS($X$3:X3,X3,$Y$3:Y3,Y3)>1. This solution assumes at least Excel 2007 (the version I have) and may not work on later versions as I can not test that. Please make sure that the $'s are as I placed them. Then choose the fill color or font color of your choice. Then copy the formatting down the column. Good luck.

  • Need help with a threshold commission formula

    Re: Need help with a threshold commission formula


    Try this. It is not the best solution, but it will work. In Cells D1:D3 put 0, 2000, 5000, in Cells E1:E3 put 2,000 5,000 999,999, in cells F1:F3 put .35, .4 ,5 and in cell G2 put this formula: =F1*(E1-D1)+G1 and copy it down to cell G3. Put your 8,000 in A1 and this formula in B1: =VLOOKUP(A1,D1:G3,3)*(A1-VLOOKUP(A1,D1:G3,1))+VLOOKUP(A1,D1:G3,4)

  • Creating If Statements

    Re: Creating If Statements


    I'm very sorry, but I can read your response in one of two ways. You could be trying to apply the IF statement to every value in column O, one cell at a time and putting the result in column P, cell by cell. In this case, if you were looking at cells O2:O10, you would get a result in each cell in P2:P10. If so, you would just create a formula in P2 and copy it down the rest of the column.


    If instead, you just want one cell in column P to contain the sum of all cells in O where the value is between 5.01 and 150, you could use this in P2 maybe:


    =SUMPRODUCT(--(O2:O10>5.01),--(O2:O10<150),3.5*O2:O10)

  • Creating If Statements

    Re: Creating If Statements


    So do you need to find all of the values in column O between 5 and 150 and multiply them by 3.5 and then sum just those? Your post is not very clear on what you need.

  • Mailing Labels With Table Data

    Re: Mailing Labels With Table Data


    I still use Word for the mail merge, but point it to my data in a spreadsheet. You need to make sure you have a header row that defines each column.

  • Minor Formula Error

    Re: Minor Formula Error


    You must have spaces or text in the cells rather than blanks, otherwise, ByTheCringe2's formula should work. To get around that you could try this modified version:


    =IF(SUM(K12:N12)=0,"",(SUM(K12,M12)*I3)+(SUM(L12,N12)*J3))

  • Run Formula Inside Function

    Re: Run Formula Inside Function


    I don't have a solution, but a function can not change a different cell, it can only return a value to the calling cell.


    Why not have a macro to populate the 200 cells when you need to run it?

  • Run Formula Inside Function

    Re: Run Formula Inside Function


    Just a quick observation. It looks like you are trying to use a function to change some cell other than the one the function is entered in. This won't work. Maybe try a macro instead.


    Also, try formatting the numeric portion to get a string like this:


    "=StacServer|" & name & "!'" & FORMAT(num + psDay,"00000") & "'"