Formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-3210613%22%20slang%3D%22en-US%22%3EFormula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3210613%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20do%20set%20off%20of%20various%20positive%20amounts%20with%201%20negative%20amount%20in%20Excel.%3C%2FP%3E%3CP%3EIs%20there%20any%20formula%20for%20this%20so%20that%20I%20can%20do%20this%20quickly.%26nbsp%3B%3C%2FP%3E%3CP%3EExample%20-%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image_2022-02-24_223114.png%22%20style%3D%22width%3A%20194px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F351067i7A1B27F58D726853%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image_2022-02-24_223114.png%22%20alt%3D%22image_2022-02-24_223114.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3210613%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3211218%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3211218%22%20slang%3D%22en-US%22%3EI%22m%20sure%20whatever%20it%20is%20that%20you're%20trying%20to%20do%20is%20possible.%20The%20problem--why%20none%20of%20the%20previous%2030%20some%20viewers%20have%20responded--is%20that%20it's%20not%20really%20clear%20what%20you're%20trying%20to%20do.%20In%20your%20example%2C%20I%20can%20see%20how%20the%20-33%20could%20relate%20to%20the%20two%20lines%20above%20it%3B%20but%20the%20-40%20doesn't%20connect%20to%20the%20positive%20lines%20above%20it.%3CBR%20%2F%3ESo%20maybe%20you%20could%20start%20over%20with%20a%20clear%20explanation%20and%20clear%20example.%20What%20actually%20do%20you%20mean%20by%20%22set%20off%22%20and%20%22do%20set%20off%22%20....and%20so%20forth%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3212210%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3212210%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20agree%20the%20-40%20may%20be%20connected%20to%201%20and%203%20(%2010%20and%2030)%3C%2FP%3E%3CP%3EI%20take%20it%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1316998%22%20target%3D%22_blank%22%3E%40Samarth_sharma%3C%2FA%3E%26nbsp%3B%20wants%20to%20have%20it%20automatically%20place%20a%20negative%20figure%20when%20the%20invoice%20is%20paid.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20can%20be%20done%20but%20a%20bit%20of%20messing%20around%20and%20different%20structure.%3C%2FP%3E%3CP%3EYou%20can%20link%20a%20formula.%20I%20have%20a%20sheet%20that%20has%20payments%20and%20such%20but%20it%20looks%20like%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3Dif(d4%3D%22Payment%22%2CE1%2Cif(d4%3D%22Interest%2CD15%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ED4%20details%3C%2FP%3E%3CP%3EE1%20payment%20value%3C%2FP%3E%3CP%3ED15%20Interest%20calulation%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3212269%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3212269%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1316276%22%20target%3D%22_blank%22%3E%40Wildecoyote1966%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20style%3D%22%20padding-left%20%3A%2030px%3B%20%22%3E%3CEM%3EI%20agree%20the%20-40%20may%20be%20connected%20to%201%20and%203%20(%2010%20and%2030)%3C%2FEM%3E%3C%2FP%3E%3CP%20style%3D%22%20padding-left%20%3A%2030px%3B%20%22%3E%3CEM%3E%3CU%3E%3CSTRONG%3EI%20take%20it%3C%2FSTRONG%3E%3C%2FU%3E%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1316998%22%20target%3D%22_blank%22%3E%40Samarth_sharma%3C%2FA%3E%26nbsp%3B%20wants%20to%20have%20it%20automatically%20place%20a%20negative%20figure%20when%20the%20invoice%20is%20paid.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20acknowledge%20that%20something%20like%20that%20is%20possibly%20what%20is%20meant.%20But%20I%20like%20to%20have%20things%20spelled%20out%20more%20clearly%20before%20devoting%20time%20to%20resolving%20a%20question%20based%20on%20assumptions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20please%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1316998%22%20target%3D%22_blank%22%3E%40Samarth_sharma%3C%2FA%3E%26nbsp%3B%2C%20help%20us%20help%20you%20by%20describing%20what%20those%20lines%20of%20particulars%20refer%20to%2C%20and%20how%20you%20expect%20the%20formula%20to%20recognize%20two%20of%20three%20lines%20of%20invoices%20that%20might%20add%20up%20to%20the%2040%20of%20a%20payment%3F%3C%2FP%3E%3CP%3EOnce%20%2440%20has%20been%20paid%2C%20do%20you%20expect%20the%20spreadsheet%20also%20to%20mark%20(somehow)%20the%20invoices%20as%20paid%3F%20Or%20will%20you%20be%20doing%20that%20manually%2C%20line%20by%20line%20yourself%3F%3C%2FP%3E%3CP%3EWhat%20happens%20if%20there%20are%20other%20combinations%20that%20add%20up%20to%20the%20amount%20of%20payment%3F%20Are%20these%20invoices%20all%20FROM%2C%20and%20payments%20all%20TO%2C%20the%20same%20outside%20party%3F%20Etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20way%20you%20have%20written%20your%20example%2C%20it%20seems%20like%20a%20very%20%3CEM%3E%3CSTRONG%3Einexact%3C%2FSTRONG%3E%3C%2FEM%3E%26nbsp%3Bor%20%3CEM%3E%3CSTRONG%3Eimprecise%3C%2FSTRONG%3E%3C%2FEM%3E%20kind%20of%20bookkeeping.%20Help%20us%20help%20you%20by%20making%20it%20more%20thorough.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3212303%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3212303%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20acknowledge%20that%20something%20like%20that%20is%20possibly%20what%20is%20meant.%20But%20%3CSTRONG%3EI%20like%20to%20have%20things%20spelled%20out%3C%2FSTRONG%3E%20more%20clearly%20before%20devoting%20time%20to%20resolving%20a%20question%20based%20on%20assumptions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20agree.%20it%20helps%20if%20there's%20a%20file%20with%20some%20sample%20info.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi 

I am trying to do set off of various positive amounts with 1 negative amount in Excel.

Is there any formula for this so that I can do this quickly. 

Example - 

image_2022-02-24_223114.png

4 Replies
I"m sure whatever it is that you're trying to do is possible. The problem--why none of the previous 30 some viewers have responded--is that it's not really clear what you're trying to do. In your example, I can see how the -33 could relate to the two lines above it; but the -40 doesn't connect to the positive lines above it.
So maybe you could start over with a clear explanation and clear example. What actually do you mean by "set off" and "do set off" ....and so forth?

@mathetes 

I agree the -40 may be connected to 1 and 3 ( 10 and 30)

I take it @Samarth_sharma  wants to have it automatically place a negative figure when the invoice is paid.

 

This can be done but a bit of messing around and different structure.

You can link a formula. I have a sheet that has payments and such but it looks like

 

=if(d4="Payment",E1,if(d4="Interest,D15,"")

D4 details

E1 payment value

D15 Interest calulation

@Wildecoyote1966 

 

I agree the -40 may be connected to 1 and 3 ( 10 and 30)

I take it @Samarth_sharma  wants to have it automatically place a negative figure when the invoice is paid.

 

I acknowledge that something like that is possibly what is meant. But I like to have things spelled out more clearly before devoting time to resolving a question based on assumptions.

 

So please, @Samarth_sharma , help us help you by describing what those lines of particulars refer to, and how you expect the formula to recognize two of three lines of invoices that might add up to the 40 of a payment?

Once $40 has been paid, do you expect the spreadsheet also to mark (somehow) the invoices as paid? Or will you be doing that manually, line by line yourself?

What happens if there are other combinations that add up to the amount of payment? Are these invoices all FROM, and payments all TO, the same outside party? Etc.

 

The way you have written your example, it seems like a very inexact or imprecise kind of bookkeeping. Help us help you by making it more thorough.

@mathetes 

I acknowledge that something like that is possibly what is meant. But I like to have things spelled out more clearly before devoting time to resolving a question based on assumptions.

 

I agree. it helps if there's a file with some sample info.