[SOLVED] Need if formulas no need to depend on helper columns

[jackson_hollon]

Hello,

I have attached a file here. The column Z and AA are depended on column W, X, and Y. I need to delete W, X, and Y as my boss hates the columns. I am looking for formulas on column Z, AA and AB that do not need to depend on column W, X and Y.

Hope I explained it good enough.

Thanks for the help.

Jackson

[JohnTopley]

How do you determine the values in the columns you want to delete?

[jackson_hollon]

John,

I have uploaded another file, it shows the formula on the deleted columns. Thanks for the help.

Jackson.

[JohnTopley]

A quick review of the formulae would indicate very complex formula (if at all possible) in Z, AA and AB which are dependent on a complex formula in "Y".

Why not simply Hide the columns W.X.Y "helper" columns (nothing wrong with using them!) or move them to some "distant" columns (out of sight, out of mind).

[jackson_hollon]

John,

I wish I could do what you suggested here. Unfortunately, my boss is the kind will not take no for an answer.

Thanks for the help.

Jackson

[JohnTopley]

If you can do it without a helper column, it is going to be a very complex formula.

I am sure you realised this - hence the helper columns: sometimes management have to realise that they are asking the "impossible".

I have played with formulae and there are "conflicts" between returning a number and and text (Y","N").

[jackson_hollon]

John,

I know my formulas are not the best, but it is the best I know how. What do you mean by there are "conflicts" between returning a number and and text (Y","N")? i would like to know where and how to fix it.

Thanks again for the help.

Jackson

[JohnTopley]

In the sense that column Y returns a value ("Accrued Months") and "Accrued Required" is dependent on the value in Y to return "Y" or "N" so trying to combine these could be problematic.

I was not suggesting anything wrong with your approach or your formulae.

How difficult is it to describe in words the business logic behind the various formulae: in particular Y, AA and AB which may help in determining whether there are alternative approaches?

If you take the formula in AB:

=IF(R9=1, EOMONTH(C9,0), IF(W9="Y",EOMONTH(T9, IFERROR(IF(AND(R9=1,DAY(B9)>16,EOMONTH(B9,1)=EOMONTH(A9,0)),"",IF(DATEDIF(IF(V9="F",EOMONTH(B9,0),EOMONTH(B9,1)),(EOMONTH(A9,0))+1,"M")=0,"",DATEDIF(IF(V9="F",EOMONTH(B9,0),EOMONTH(B9,1)),(EOMONTH(A9,0))+1,"M"))),"")),T9))

This checks W for "Y" which derived from the formula in column Y

=IFERROR(IF(AND(R9=1,DAY(B9)>16,EOMONTH(B9,1)=EOMONTH(A9,0)),"",IF(DATEDIF(IF(V9="F",EOMONTH(B9,0),EOMONTH(B9,1)),(EOMONTH(A9,0))+1,"M")=0,"",DATEDIF(IF(V9="F",EOMONTH(B9,0),EOMONTH(B9,1)),(EOMONTH(A9,0))+1,"M"))),"")

so effectivly we have to place the "Y" result with something like the column Y formula.!!!

I think most people would take your approach of the "helper" columns: I don't understand why (some) people dislike them so much.

[jackson_hollon]

John,

We have to send this file to our investors. According to my boss, the file needs to be "Clean! clean! clean! no extra columns so that they will not have questions." I had worked on this file for 2 weeks already. I can't think of a way that these extra columns can be deleted.

Thanks for the help.

[JohnTopley]

I only wish I could help more as I am of the same view as you the "helper" columns are needed.

[dmg2016]

Hi Jackson,

To remove the helper columns, all you need to do is use named formulas.

Accrued_Months:

Please Login or Register  to view this content.

*Must be down first because other helper columns are dependent

Accrued Amount:

Please Login or Register  to view this content.

Accrued Required

Please Login or Register  to view this content.

Prepaid Amount:

Please Login or Register  to view this content.

Notice the mixed referencing in my formulas because without it the named ranges will not work properly.

-DMG

[JohnTopley]

I see DMG2016 has a neat solution. I must admit I have never used named formulas although I have seen forum examples of such.

Looking again ...

in AA

=R9-IFERROR(IF(AND(R9=1,DAY(B9)>16,EOMONTH(B9,1)=EOMONTH(A9,0)),0,IF(DATEDIF(IF(V9="F",EOMONTH(B9,0),EOMONTH(B9,1)),(EOMONTH(A9,0))+1,"M")=0,0,DATEDIF(IF(V9="F",EOMONTH(B9,0),EOMONTH(B9,1)),(EOMONTH(A9,0))+1,"M"))),0)

in Z

=O9-(R9-AA9)*S9

These remove need for X and Y (i hope!)

Can you change (simplify) AB which appears to be same as T? Why isn't it?

[dmg2016]

Here is the non-neat solution. It can obviously be simplified in parts.

Row 9 Formulas
Prepaid Amount:

Please Login or Register  to view this content.

Prepaid Months:

Please Login or Register  to view this content.

Prepaid Begin Month:

Please Login or Register  to view this content.

[jason.b75]

I think these are right, but trying to decrypt train-wreck formulas with no explanation of logic is no easy task.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

These all return the same results as the formulas with helper columns in the sample, but will need to be verified with other criteria combinations.

[jackson_hollon]

Jason, John and dmg,

Thank you so much for the help. I learned a lot from your answers. With your solutions, it seems it works. I need to play around a little more. But I want to thank you for the help first.

Jackson

[jackson_hollon]

John,

AB is not the same as T. If accrued required, the month will be different.

Thanks.

Jackson

[jackson_hollon]

John,

AB is not the same as T. If accrued required, the month will be different.

Thanks.

Jackson

[jason.b75]

You're welcome, Jackson!

Just to point out, the formulas that I provided are based on what little logic I was able to figure out from your existing formulas.

If you can explain what the real world criteria is for each of the 3 columns then I'm sure that we can come up with some better formulas.

edit:-

Prepaid Amount (Z9)

=O9-((R9-AA9)*S9)

Which could be simplified to =S9*AA9 as long as the Exp Per Period column remains unrounded.

Prepaid months

=R9-DATEDIF(T9,AB9,"M")

Need to be able to understand the logic of Prepaid Begin Month before I can do anything with that one.

I noticed that there is no check for "F" in V9 in this part of the formula, AND(R9=1,DAY(B9)>16,EOMONTH(B9,1)=EOMONTH(A9,0))

Does this mean that the F / L flag in column V is irrelevant in this situation, or has it been overlooked?

[jackson_hollon]

Jason,

The V column is key in value. I am still thinking on this formula. The logic is if column B is before 15 of the month, then amortize it on the same month (F), otherwise, it will amortize from the following month (L).

However, the formula for the column AB is if the column R is only one month, then column AB is the same month of the column c.

I used your formula for column AB, when it comes to only one month, the formula doesn't work. So I changed the formula to

=IF(R9=1,EOMONTH(C9,0),EOMONTH(T9,IFERROR(IF(AND(R9=1,DAY(B9)>16,EOMONTH(B9,1)=EOMONTH(A9,0)),"",DATEDIF(IF(V9="F",EOMONTH(B9,0),EOMONTH(B9,1)),(EOMONTH(A9,0))+1,"M")),0)))

It works perfectly. I learned a lot on what I should do with your formula.

Thanks for the help again.

Jackson

[jason.b75]

I'm having trouble getting my head around the logic, could you attach another sample with an entry for 1 month in AA and enter the expected results manually.

I have the formula down to this for AB9

=EOMONTH(T9,IFERROR(DATEDIF(EOMONTH(B9-15,1),EOMONTH(A9,0),"M"),0))

Which removes the need for column V as well, but based on your comments above, I think it needs some changes.

[jackson_hollon]

Jason,

I had uploaded a newest version of the file.

Thanks again for the help.

Jackson

[jason.b75]

Looking at the formula you say works perfectly in post #19, what would be the correct date in AB19?

You have manually entered May-14, but the formula returns Apr-14.

This returns the same,

=IF(R9=1,EOMONTH(C9,0),EOMONTH(T9,IFERROR(DATEDIF(EOMONTH(B9-15,1),EOMONTH(A9,0),"M"),0)))

But is only good if Apr-14 is correct for AB19.

The logic behind selecting the date is still a bit confusing.

[jackson_hollon]

John,

Sorry, I made a mistake. The T19 should be Mar 14, and the AB19 should be Apr 14. I keyed in the data wrong.

Sorry about it.

[jason.b75]

In that case you should find that the formula in post #22 used in AB9, along with the formulas in post #18 used in Z9 and AA9 will give you what is needed and allow you to delete the helper columns (column V can also be deleted if that's part of the requirement).

[jackson_hollon]

Jason,

Thank you very much for the help. I very appreciated the time you had put in to this thread.

Thank you again.

Jackson