Conditional Output

[rachelroseleonardo]

Hi Everyone, I have a question to ask and I am not sure if this is the right Category to post it.

I work as a secretary and my boss asked me to do some Excel conditional work and although I have some limited knowledge of basic programming learned in college, I have no idea whatsoever to approach this in Excel. I would really appreciate if you guys would lend me a helping hand

Without further ado, let me explain my dilemma.

I have an Excel Spreadsheet consisting of a lot of technical data. Now for reasons beyond my understanding, my boss asked me to formulate a unique condition.

I attached an example of the spreadsheet to hopefully give you an idea of what I mean.

**[Condition.xls]**
I created two tables inside the spreadsheet - Original and Example. The Original is the format required ultimately as it is in the orginal spreadsheet and the Example is for explaining the condition required.

EXAMPLE Table:
Fields "Total" and "Seniority" are obtained separately.
"Total x Seniority" is the product of "Total" and "Seniority".
"Cumulative" is obtained separately.
"-12.5% of T x S" is 12.5% of "Total x Seniority"
"25% of T x S" is 25% of "Total x Seniority"

Now for the Condition,
If the value of "Cumulative" lies between the lower bound of "-12.5% of T x S" and the upper bound of "25% of T x S", then there are no issues and the CONDITION column says "YES" and the Difference is n/a or 0.

But if the value of "Cumulative" does not lie between the lower bound of "-12.5% of T x S" and the upper bound of "25% of T x S",
then the difference between the BOUND that is crossed over and the "Cumulative"
must be automatically input into the "DIFFERENCE".

For Example, lets take the third row of the EXAMPLE table.
Total = 25000 , Seniority = 1; (given)
Therefore, Total x Seniority = 25000 x 1 = 25000.

Cumulative = 17000 (given)
"-12.5% of T x S" = (-0.125) x 25000 = -3125
"25% of T x S" = (0.25) x 25000 = 6250

Checking Condition, it is understood that that 17000 does not lie between the lower bound of -3125 and 6250. It has crossed over the upper bound of 6250.
Hence, the difference is
Cumulative - (crossed-over Bound)
= 17000 - 6250
= 10750

I have input some more examples into the spreadsheet, hopefully that will make this obscure condition clearer.

Now the only fields that are required are the ones in the ORIGINAL table. The rest are just examples. I sincerely hope that it is possible to formulate a solution to calculate and input the DIFFERENCE value automatically. I would really appreciate any help to help me tackle this problem. Thank you for reading this really long post and have a nice day.

[ConneXionLost]

In cell E16 try:

Please Login or Register  to view this content.

Cheers,

[rachelroseleonardo]

That works like a charm! Just what I needed!!
Thanks a ton. :D :D

Added Reputation too! Well Deserved! :D