Home

A complex IF, Then statement.

%3CLINGO-SUB%20id%3D%22lingo-sub-918850%22%20slang%3D%22en-US%22%3EA%20complex%20IF%2C%20Then%20statement.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-918850%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20established%20Repair%20Order%20Work%20Form.%20I'm%20hoping%20to%20explain%20this%20well%20enough.%20That%20someone%20is%20able%20to%20help%20me%20write%20the%20formula%20I%20need.%20I%20have%20basically%204%20columns%20A%20is%20the%20amount%20of%20items%20sold%2C%20B%20is%20a%20description.%20C%20is%20price%20per%20unit.%20D%20is%20the%20total%20for%20the%20row.%20All%20that%20works%2C%20both%20across%20and%20down.%3C%2FP%3E%3CP%3EUNDER%20the%20total.%20There%20is%20room.%20for%201%20more%20row.%26nbsp%3B%20I'm%20looking%20for%20to%20add%20this%20statement%3C%2FP%3E%3CP%3E(%3DIF%20A27%20%3D%201%2C%20Then%20B27%20print%20%22Preferred%20Customer%20Discount%22%2C%20also%20C27%20is%20minus%2015%25%20of%20D26%20with%20that%20result%20in%20cell%20D27.%3C%2FP%3E%3CP%3EThis%20formula%20does%20the%20correct%20Math%2C%20but%20is%20also%20an%20error%2C%20I'm%20not%20sure%20what.%26nbsp%3B%3C%2FP%3E%3CP%3E%5B%3DIF%20(F30)%20%3D%201%20Than%20SUM(F29*-15%25%2BF29)%5D%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20discount%20certain%20work%20orders%20IF%20and%20only%20IF%20A27%20%3D%201%20Then%20I%20need%20it%20to%20insert%26nbsp%3B%20the%20statement%20above%2C%20subtract%2015%25%20of%20the%20current%20total%20from%20D26.%20And%20display%20the%20answer%20in%20D27.%3C%2FP%3E%3CP%3EThis%20is%20a%20formula%2C%20I%20would%20keep%20in%20an%20open%20notepad%20for%20use%20when%20I%20am%20directed%20by%20the%20owner.%20I%20can%20copy%20%2F%20paste%20into%20C27..%20With%20all%20that%20being%20said%2C%20If%20A27%20%3D%20%22nothing%22%20I%20don't%20want%20the%20formula%20to%20do%20anything%20at%20all.%26nbsp%3B%3C%2FP%3E%3CP%3ECurrently%20my%20totals%20including%20tax%20etc.%20Are%20based%20on%20the%20results%20form%20D26..%20I%20may%20have%20to%20write%20a%20secondary%20workbook%20to%20allow%20the%20product%20of%20all%20my%20sub%20totals.....%20ie.%201%20workbook%20for%20D26%20%2B%20tax%2C%20and%201%20workbook%20for%20D27%2Btax.%20To%20ask%20Excel%20to%20decide%20which%20total%20to%20use%2C%20is%20probably%20far%20beyond%20its%20capability.%3C%2FP%3E%3CP%3EAltering%20the%20totals%20formula%20used%20in%20Column%20%22L%22%20to%20use%20D26%20or%20D27%2C%20shouldn't%20be%20a%20problem%20to%20fix%20each%20time.%3C%2FP%3E%3CP%3EAny%20idea%20are%20Welcome%2C%20It%20got%20to%20be%20easier%20than%20doing%20this%20ALL%20by%20hand%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-918850%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EIF%20Formula%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-919727%22%20slang%3D%22en-US%22%3ERe%3A%20A%20complex%20IF%2C%20Then%20statement.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-919727%22%20slang%3D%22en-US%22%3EThe%20formula%20in%20D27%20could%20be%3A%3CBR%20%2F%3E%3DIF(A27%3D1%2C%3CBR%20%2F%3ED26*0.85%2C%3CBR%20%2F%3E%E2%80%9C%E2%80%9D)%3C%2FLINGO-BODY%3E
mbotti215
Occasional Visitor

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

 

1 Reply
The formula in D27 could be:
=IF(A27=1,
D26*0.85,
“”)
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies