Formula help, several steps

Camel123

Board Regular
Joined
Jun 5, 2018
Messages
186
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?
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Re: Return column of far most right value in a row

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

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?
 
Upvote 0
In I3 enter following:
Code:
=--(DATE(LEFT($H1,4),RIGHT($H1,2),1)<=DATE(LEFT(I1,4),RIGHT(I1,2),1))*$B2/$C2
Then drag to the right
 
Upvote 0
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 (?)
 
Last edited:
Upvote 0
@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
 
Last edited:
Upvote 0
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 :/
 
Upvote 0
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
 
Last edited:
Upvote 0
Re: Return column of far most right value in a row

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

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?
 
Last edited:
Upvote 0
Re: Return column of far most right value in a row

Did you try suggestion in #3 ?
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top