Formula help, several steps

[Camel123]

Hi,

Asked for help regarding below in a different thread but since it evolved to be very disconnected from the subject/title I am reposting it as a separate thread:

Please see picture, https://imgur.com/28MVScU

I want the formula to do the following:

Divide the costs in col B with the number of months in col C,
and return this value to all dates (in the green colored field) that are equal to, or greater than
the date of the far most right value in the blue colored field.

Any suggestions?

 

[JackDanIce]

Re: Return column of far most right value in a row

I tried it, unfrtunately did not work as intended to :/

- anything more specific since your screen isn't visible on the thread...

Maybe try this change:

=--(DATE(LEFT($H1,4),RIGHT($H1,2),1)>=DATE(LEFT(I1,4),RIGHT(I1,2),1))*$B2/$C2

The more precise information you can give, easier it is to suggest a solution.

A previous reply stated

I have no idea how that relates to what you asked originally

suggesting your explanations are unclear and ambigious hence proving difficult to provide a working solution.

 

[jasonb75]

Re: Return column of far most right value in a row

So, in the actual file I wrote the following formula:

=IF(DATEVALUE(BY$6&"/1")>=AGGREGATE(14;6;DATEVALUE($AC$6:$AZ$6&"/1")/($AC$8:$AZ$8<>"");1);$Q8/$X8;"")

I changed some of the $'s to drag to the right and all the way down, all the references refer to the same information as the simulated example in the picture I linked, same format. Though it does not work. Evaluating the formula in several cells and it seems like >=AGGREGATE(14;6;DATEVALUE($AC$6:$AZ$6&"/1")/($AC$8:$AZ$8<>"");1) is always referred to as 2020/12/01 (value 44166), probably the reason for not working. Any suggestions on how to solve this?

How are the dates in your actual file formatted? The dates in the picture appeared to be text so the formula was based on that. If your proper file contains real dates then it will most likely fail.

One observation, should ($AC$8:$AZ$8<>"") actually be ($AC8:$AZ8<>"") if you're filing down?

Also check that the empty cells in AC8:AZ8 are actually empty, data imported from other sources can sometimes have stray characters that might cause excel to think that the cell has content.

If those cells contain formulas, then check that blanks are returned as "" and not " ". A space between the double quotes is a common cause of failure.

 

[Yongle]

Re: Return column of far most right value in a row

Asked for help regarding below in a different thread but since it evolved to be very disconnected from the subject/title I am reposting it as a separate thread:

It did not evolve - you asked the wrong question in your original post and then moved the goalposts

 

[Camel123]

Re: Return column of far most right value in a row

@JackDanIce @jasonb75

Sorry for the unclear description... I uploaded a new picture for you to check, from the actual file (had to hide and clear some info, though not included in the formula): https://imgur.com/Qzph1z2

They are both formed as custom dates, same custom format on all: https://imgur.com/a/RzzQYas

Even on the first row where formulas are not pulled down, it does not work and the issue is the same (as you can see on the picture). AC8:AZ8 does contain formulas, blanks are defined as "", double checked. Appreciate your efforts trying to solve this, thank you guys!

 

[jasonb75]

Re: Return column of far most right value in a row

Looking at your current evaluation in the image, AH8 and AK8:AZ8 are all evaluating as not blank. AC8:AG8 and AI8:AJ8 are evaluating as blank. The only way to get the formula working is to find why this is happening. This is not something that we can do for you as we do not have access to the original file.

Try evaluating the formula in AL8 to see what the actual final result is.

Do you have conditional formatting that masks certain results, or perhaps the option to show a zero in cells that have a zero value is un-checked? File > Excel Options > Advanced > Display options for this worksheet.

 

[Camel123]

Re: Return column of far most right value in a row

Looking at your current evaluation in the image, AH8 and AK8:AZ8 are all evaluating as not blank. AC8:AG8 and AI8:AJ8 are evaluating as blank. The only way to get the formula working is to find why this is happening. This is not something that we can do for you as we do not have access to the original file.

Try evaluating the formula in AL8 to see what the actual final result is.

Do you have conditional formatting that masks certain results, or perhaps the option to show a zero in cells that have a zero value is un-checked? File > Excel Options > Advanced > Display options for this worksheet.

Solved it thanks to your hints! So, the issue was some of the references in the formula being blank while others have formulas returning 0's. I changed the formulas to return "" which made all values to appear! Finally :D thank you! Now I'm just gonna try to understand the content of the formula and all its steps, to learn something form this, you might get another question or two

 

[Camel123]

Hi,

Asked for help regarding below in a different thread but since it evolved to be very disconnected from the subject/title I am reposting it as a separate thread:

Please see picture, https://imgur.com/28MVScU

I want the formula to do the following:

Divide the costs in col B with the number of months in col C,
and return this value to all dates (in the green colored field) that are equal to, or greater than
the date of the far most right value in the blue colored field.

Any suggestions?

Just realized I made a mistake. The formula should do (almost the same as initially described) but only for one difference:

Divide the costs in col B with the number of months in col C,
and return this value to all dates (in the green colored field) that are equal to, or greater than
the date of the far most right value in the blue colored field, BUT maximum the date of the far most right value + number of months according to col C.

 

[jasonb75]

I'm not sure that I follow correctly, could you post an image showing columns where the current formula is giving the incorrect results, with the results that you need typed into the row below. Thanks.