Lengthy formula !!

[ramNrocky]

Hi can anyone please help me in reducing the length of this formula
=IF(SUM(W7:Y7)<T7,(SUM(W7:Y7)/T7),IF(SUM(W7:Y7)>T7,(SUM(W7:Y7)-T7)/U7)+1)

I'm a beginner level user of excel.
Kindly help ASAP

Thanks in Advance.

[FDibbins]

If you only have those 2 tests, then it looks like you dont need that 2nd IF...

=IF(SUM(W7:Y7)<T7,(SUM(W7:Y7)/T7),(SUM(W7:Y7)-T7)/U7)+1)
what happens if it = T7?

[ramNrocky]

Hey thanks for the quick reply.
I'll explain you the situation can you help me with the formula...?

If SUM(W7:Y7)<T7 then it should be SUM(W7:Y7)/T7
If SUM(W7:Y7)>T7 & <SUM(T7,U7) then it should be (SUM(W7:Y7)-T7)/U7)+1
If SUM(W7:Y7)>SUM(T7,U7) then it should be (SUM(W7:Y7)-SUM(T7,U7)/V7)+2

I need a formula which satisfies the above conditions.
As i have mentioned in the previous thread I'm a beginner level user of excel.
It would be of great help if u help me with the formula.

Thanks in Advance.

[FDibbins]

A shot in the dark...
=if(SUM(W7:Y7)>t7,SUM(W7:Y7)/t7, if(and(SUM(W7:Y7)>t7,SUM(W7:Y7)<t7+u7,((SUM(W7:Y7)-t7)/u7)+1, (SUM(W7:Y7)-SUM(T7,U7)/V7)+2))

(it's late and my brain went to bed long before me lol)

[ramNrocky]

Hey Thanks for the reply but it didn't work

[Glenn Kennedy]

Your requirement is ambiguous. What if the sum = T7, or =sum T7+U7???

[ramNrocky]

In my case it wil never be equal to T7 or T7+U7 !!

I know i'm being naive. But believe me i have done manual calculations on paper so i can say that it won't be equal.

[AliGW]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Glenn Kennedy]

Try this out:

=IF(SUM(W7:Y7)<T7,SUM(W7:Y7)/T7,IF(AND(SUM(W7:Y7)>T7,SUM(W7:Y7)<SUM(T7,U7)),1+(SUM(W7:Y7)-T7)/U7,IF(AND(SUM(W7:Y7)>T7,SUM(W7:Y7)>SUM(T7,U7)),2+(SUM(W7:Y7)-T7)/V7)))

[ramNrocky]

Hey guys i have finally got the formula which gave the correct answers....

=IF(SUM(W7:Y7)<T7,SUM(W7:Y7)/T7,IF(AND(SUM(W7:Y7)>T7,SUM(W7:Y7)<SUM(T7,U7)),1+(SUM(W7:Y7)-T7)/U7,IF(SUM(W7:Y7)>T7+U7,(SUM(W7:Y7)-SUM(T7,U7))/V7)+2))

ThanK you Glenn, with some modifications to your formula i got the result.

Now is it possible to make it shorter...?

[AliGW]

As I said before, attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Glenn Kennedy]

Why do you want to make it shorter. It's not long... and if it works???

[ramNrocky]

Hey ali I'm attaching the file....plz help me

Refer to the column Z (payback absolute)

[ramNrocky]

Glenn its fine for me and its working too. But my friend is insisting me to shorten it. so i'm asking

[Pete_UK]

If you used a helper column (let's say column Z) with this formula in Z7:

=SUM(W7:Y7)

then your formula from Post #10 would become:

=IF(Z7<T7,Z7/T7,IF(AND(Z7>T7,Z7<SUM(T7,U7)),1+(Z7-T7)/U7,IF(Z7>T7+U7,(Z7-SUM(T7,U7))/V7)+2))

as that expression occurs several time in the formula.

Hope this helps.

Pete

[ramNrocky]

But that is Ony for row 7. I got a total of 42 rows. How do i use a helper column in that case ?

[Pete_UK]

You copy the formulae down to cover the 42 rows.

Hope this helps.

Pete

[sanram]

In my case it wil never be equal to T7 or T7+U7 !!

I know i'm being naive. But believe me i have done manual calculations on paper so i can say that it won't be equal.

As you stated here if they will never be equal then you can use this:

Please Login or Register  to view this content.

[ramNrocky]

That doesn't help @sanram

Thank you for your reply.

[sanram]

That doesn't help @sanram

Thank you for your reply.

Oh, Really!! But it was working on my system. You can see the attachment. But sorry that I couldn't help.