EXCEL : Conditional Output

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

rachelrleonardo

Thread Starter
Joined
Dec 3, 2011
Messages
2
Hi Everyone, I have a question to ask and I am not sure if this is the right Category to post it. :confused:

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.:confused:

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. :)
 

Attachments

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,484
i think this IF statement works

=IF(D16<(A16*B16*(-0.125)),(D16-(A16*B16*(-0.125))),IF(D16>(A4*B4*0.25),D16-(A4*B4*0.25),0))

I have attached the spreadsheet and entered the formula in cell F16 , so the output can be compared
I have used greater than so if the condition = the test than it will be false -
if you want to say is greater/less or equal to
then change the
>
or
<
to
>=
<=

that will apply if it equals the condition and limits
=IF(D16<=(A16*B16*(-0.125)),(D16-(A16*B16*(-0.125))),IF(D16>=(A4*B4*0.25),D16-(A4*B4*0.25),0))


so this is doing a nested IF

IF( test, true , false )
nest another IF where its false

IF ( test, true, (IF( test, true, false)) )

so the first test is to check if the value is less than the lower limit , if it is then in works out the true part - , but if false - then we add another test to see if the value is greater than the higher limit if it is then we do the next true part - if not then it must lie inbetween and so we do the false part - which is zero

IF(Test for greater than , True , IF(Test for less than , True, false) )

does that make sense ?

note - I used the formulas for working out the upper and lower limit direct from your example - so have not checked the arithmetic used to calculate the -12.5% or the +25% -
 

Attachments

rachelrleonardo

Thread Starter
Joined
Dec 3, 2011
Messages
2
Thank you for your elaborate reply, Etaf :)

I tested out some examples with my test-file and they seem to work beautifully.
I had some dumb questions regarding, but I managed to get it right. ;)

Thanks again and warmest regards.
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,484
your welcome, no such thing as a dumb question ......
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top