A complex IF, Then statement.
I have an established Repair Order Work Form. I'm hoping to explain this well enough. That someone is able to help me write the formula I need. I have basically 4 columns A is the amount of items sold, B is a description. C is price per unit. D is the total for the row. All that works, both across and down. UNDER the total. There is room. for 1 more row. I'm looking for to add this statement (=IF A27 = 1, Then B27 print "Preferred Customer Discount", also C27 is minus 15% of D26 with that result in cell D27. This formula does the correct Math, but is also an error, I'm not sure what. [=IF (F30) = 1 Than SUM(F29*-15%+F29)] I am trying to discount certain work orders IF and only IF A27 = 1 Then I need it to insert the statement above, subtract 15% of the current total from D26. And display the answer in D27. This is a formula, I would keep in an open notepad for use when I am directed by the owner. I can copy / paste into C27.. With all that being said, If A27 = "nothing" I don't want the formula to do anything at all. Currently my totals including tax etc. Are based on the results form D26.. I may have to write a secondary workbook to allow the product of all my sub totals..... ie. 1 workbook for D26 + tax, and 1 workbook for D27+tax. To ask Excel to decide which total to use, is probably far beyond its capability. Altering the totals formula used in Column "L" to use D26 or D27, shouldn't be a problem to fix each time. Any idea are Welcome, It got to be easier than doing this ALL by hand
If Formula Error
I am trying to use the if function below to display employee performance rankings. I created a base sheet to compile data from other sheets, and I created the equation below: =IF(Base!E3=1, Base!A3, IF(Base!E4=1, Base!A4, If(Base!E5=1, Base!a5, if(Base!E6=1, Base!A6, If(Base!E7=1, Base!A7, If(Base!E8=1, Base!A8, If Base!E9=1, Base!A9, If(Base!E10=1, Base!A10, If(Base!E11=1, Base!A11, If(Base!E12=1, Base!A12, IF(Base!E13=1, Base!A13, If Base!E14=1, Base!E14)))))))))))) However, when hit enter to go to the next box and make its formula, I get a error that says I have too many arguements. Using Excel's built in F1 help menus, "You can nest up to 64 levels of functions in a formula." I have far less than 64 Levels. I am looking for some help as to how I can complete my task, as the formula in all cells I am looking to fill in need to have the same structure to it.
Vlookup for when it can't find AND when it's blank
I currently have the following formula set up =IFERROR(VLOOKUP(C163,Validator!K:L,2,FALSE),"Click to learn more") That means it looks up text in another table to find a result, and if the title in C163 isn't there it returns the value 'click to learn more.' This is great, but i would also like to add an extra layer. If C163 is blank (ie its not that the text can't be found in the lookup table, its that cell C163 is completely blank) I would like it to return an empty cell. Is that possible? How can i do that?
IF Formula trouble
I'm almost embarrassed to ask this, but having a heck of a time finding an answer. I'm tracking Gross Commissions for salespeople at my company, and when they reach a specific target their commission split changes to a higher split. This is the formula I'm using: =IF($D$1<$F$3,(E6-F6)*$B$3,(E6-F6)*$D$3) $B$3 is 80%. $D$3 is 85% The problem I'm having: Once the commission target is reached and the split goes from $B$3 to $D$3 all of the previous commissions that were calculated at $B$3 are recalculated at $D$3. Is there a way to lock the formula at $B$3 once the value has been calculated? I've been working on this for a week and I can't afford to pull out more hair.Sort If Function List Results
I have a list of 200 names, with emails, year of graduation and enrollment date. I am trying to extract a list of emails depending on the YoG and ED. My current formula works (e.g. it only it shows the emails with the criteria I ask for) but the emails in the new list correspond to the cell it is in, making it so I have five emails separated by 195 blanks. I want to arrange all the TRUE answers at the top, and leave the FALSE answers at the bottom, so all the emails appear at the top of the list, and all the blanks at the bottom. Current Formula: =IF(AND(H4=$K$2,E4=$K$7),C4,"") Translation: If entry has the text in Column H that corresponds with the text in K2 AND the text in Column E corresponds with the text in K7, give me the data in Column C. If not, leave the cell blank. Right now the formula produces this list in Column N: [Blank] [Blank] Email [Blank] [Blank} Email [Blank] Email But I want it to be: Email Email Email Email [Blank] [Blank]
Help with Conditional Formatting
Hi! I can't find a way to make this conditional formatting work how I need. The columns that I am working with are A, E and F. I need to set up conditional formatting for column A. ID (A) Prefix (B) FirstName (C) LastName (D) Phone (E) Complete (F) 63077 Mrs. First Last Phone x 171015 Mrs. First Last 26473 Mrs. First Last x 6685 Mr & Mrs First Last Phone x 127225 First Last Phone x The formatting should be as follows: If E is blank/empty and F is blank/empty, A is filled Yellow. If E is blank/empty and F has "x" value, Stop Rule. Can someone help me figure out how to do this? Thanks!