• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need Comprehensive Excel Formula

A

akurathi

New Member
I need a comprehensive excel formula for the following data. I am using Excel 2007.

There are certain amounts in Cells B1, C1, D1 AND G1. Now I want that the negative amount in C1 should be setoff with the positive amounts in B1 & D1. While setoff, the following things to be taken care of.

1) The negative amount in cell C1 should be setoff with the positive amount which has highest percentage and if the amount in D1 is not sufficient then the balance negative amount has to be setoff with the next percentage amount B1.

2) After that the negative amount in cell G1 should be set off with the positive amounts in cells B1 to D1 (subject to availability of positive amount).

3) Ignore, if cell G1 has a positive number or insufficient balance in cells B1 to D1 to cover negative amount in cell G1.

1725861636500.png
 
What are the expected values?

This:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

A​
B​
C​
D​
5​
Result
xxx​
xxx​
xxx​
Sheet: Sheet1

does not tell us what we are aiming for. Please calculate manually and add these in.
 
Madam,
Another Important point I forgot to mention, that the amounts may change from positive to negative in cells and the B1 to D1 and the percentages also may change in cells B3 to D3. Sorry for the inconvenience.
 
After the positive and negative amounts are adjusted then the amounts are multiplied with percentages to arrive the values.
 
And the expected results are ... ???

I want the values that you are expecting in row 5, please, for the sample data that you have entered in row 1.
 
And what should the amounts be for the data as it is in the sample? I asked for VALUES, not a description.
 
I calculated manually, but I want a formula to automatically calculate as shown in the attached workbook.
 

Attachments

  • Need Formula.xlsx
    9.3 KB · Views: 1

akurathi​

Please reread Forum Rules, which are for everyone
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
 
You must log in or register to reply here.
Back
Top