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?

 

[jasonb75]

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

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

I can see from the image that your dates are not actually dates but are in fact text strings that look like dates, this means a bit of extra manipulation to make things work.
Try this in I2, then drag right.

=IF(DATEVALUE(I1&"/1")>=AGGREGATE(14,6,DATEVALUE($D$1:$H$1&"/1")/($D$2:$H$2<>""),1),$B$2/$C$2,"")

Does it do as needed?

 

[JackDanIce]

In I3 enter following:

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

Then drag to the right

 

[Camel123]

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

@jasonb75

First I'm gonna try to get it working and then I will ask all other questions to understand the evaluation

So, it returns a value but in the wrong month, instead of returning the B/C value in 2019/09 it starts appearing in 2020/12 (?)

 

[Fluff]

@Cameltoe
Please do not post the same question multiple times. (rule 12 here: Forum Rules).
I have removed your final post from your previous thread & moved the response to this thread which is now post#2 in case you hadn't seen it

 

[Camel123]

Did it work for you? I tried it but and the formula retrieve values but not only for the "green" dates that are equal to or greater than the "blue" date for the far most right cell that contain a value :/

 

[Camel123]

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

@jasonb75
Now I got it working correct in the file I used to simulate the actual one (since it is corporate confidential content). Using it in the actual file does not work correct, probably me doing something wrong, I'm gonna try to solve it before asking you for help again :D

 

[Camel123]

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

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

I can see from the image that your dates are not actually dates but are in fact text strings that look like dates, this means a bit of extra manipulation to make things work.
Try this in I2, then drag right.

=IF(DATEVALUE(I1&"/1")>=AGGREGATE(14,6,DATEVALUE($D$1:$H$1&"/1")/($D$2:$H$2<>""),1),$B$2/$C$2,"")

Does it do as needed?

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?

 

[JackDanIce]

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

Did you try suggestion in #3 ?

 

[Camel123]

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

Did you try suggestion in #3 ?

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