Hi guys, I need some help with the formula to create a calculated columns in excel.

Question

Hi guys, I need some help with the formula to create a calculated columns in excel.

Anonymous

Hi guys, I need some help with the formula to create a calculated columns in excel.
Considering columns A to AC, I need to calculate F to AC columns.

I need to calculate columns with the logic :

IF the Client A has Duration Months contact of 3 months, Forecast Begin Date starts from 01-10-2022 (October 1, 2022) and Potential MRR (monthly contract value) $75

So, if the contract starts from October 1, 2022 for 3 months then the result should show in columns O, P Q and rest for the columns should show as 0.

Similarly, Client B result should show for 3 months in columns H, I and J as $50 (contract starts from 01-03-2022, "March 1, 2022", rest as 0)

Thanks in advance

180397-pipeline.txt

Accepted answer

1 additional answer

Your answer

Answer 1

Emily Hua-MSFT 27,796

Hi @SumiteshKumar-6515

Please check whether the following information is helpful.

First, I suggest you custom the cell formart for F1 to AC1, that using yyyy - mmm to show the Year and Month as the image below.

Then try this formula =IF(AND(F$1>=$C2,F$1<=DATE(YEAR(EDATE($C2,$B2-1)),MONTH(EDATE($C2,$B2-1)),1)),$D2,0) in cell F2, then dag it down and right to fill the Potential MRR or value 0 into cells.

Any misunderstandings, please let me know.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Anonymous

2022-03-07T09:44:56.32+00:00

Hi Emily,

Thanks for your reply.
I tried using the same but the formula is not showing up Potential MMR in the following months.

For reference please look into the snapshot attached.

Best
Emily Hua-MSFT 27,796 Reputation points

2022-03-08T08:49:11.207+00:00

Hi @SumiteshKumar-6515
Thanks for your detail reply.
According to your screenshots, I would also suggest you set cell format for Potential MMR and other cells with currency.

Then you could try your formula =IF(AND(N$2>=$H3,N$2<=DATE(YEAR(EDATE($H3,$E3-1)),MONTH(EDATE($H3,$E3-1)),1)),$K3,0).
Anonymous

2022-03-08T11:05:01.627+00:00

Hi Emily,

I formatted the cells POTENTIAL MRR, POTENTIAL TCV AND COLUMN N TO COLUMN AK AS (CURRENCY $), formatted DURATION MONTHS as (DATE), 2022 - Jan as (YYYY MMM) and used this formula to populate POTENTIAL MRR to the mentioned months as per FORCAST BEGIN DATE, still it shows $0 in the required months.

Please suggest.

Thanks
Emily Hua-MSFT 27,796 Reputation points

2022-03-09T09:51:26.02+00:00

Hi,

> formatted DURATION MONTHS as (DATE)

Please check the number format of DURATION MONTHS.
Anonymous

2022-03-09T10:04:42.967+00:00

Hi Emily, tried this aswell, didn't work.
Can we do this is sharepoint as Calculated (calculation based on other columns)?
The prepose of the formula used is to show the potential MRR in required months as per the forecast beginning date.

I am using sharepoint for opportunity tracking but pipeline report we created in excel, using date from sharepoint query (as we discussed)

If you can help me creating syntax in sharepoint.
We have the values of forecast begin date, potential MRR and duration months which is coming from sharepoint.

Thanks
Emily Hua-MSFT 27,796 Reputation points

2022-03-10T09:53:25.49+00:00

Hi @ SumiteshKumar-6515
Thanks for your reply ~
But I am not familar with syntax in sharepoint, to help you better, I find that you have posted a new thread with SharePoint tag (https://learn.microsoft.com/en-us/answers/questions/765209/hi-guys-i-need-some-help-with-the-syntax-to-create-1.html), please be patient with advice from SPO's experts.
Thanks for your understanding.

But if this issue on Excel dekstop client, I would suggest you check the data format of Windows, and check the result of formula `=I3+1", judging whether it is 2022, March 2.
Anonymous

2022-03-13T07:34:45.23+00:00

Thanks Emily that worked

Answer 2

Herbert Seidenberg 1,191

Excel 365 Pro Plus with Power Query and Power Pivot.
As Emily so rightly shows, it's all about formatting.
Since PQ tracks, labels, flags and enforces Types of data
( numbers, dates, times, text, etc ) very well,
I suggest trying Power Query.
No formulas, no VBA macro.
With PivotChart.
https://www.mediafire.com/file/wpw0eblyj5ywycg/03_11_22.xlsx/file
https://www.mediafire.com/file/cpk5xl6c532s38s/03_11_22.pdf/file

Anonymous

2022-03-13T07:33:50.837+00:00

Thanks Herbert

Share via

Hi guys, I need some help with the formula to create a calculated columns in excel.

1 additional answer

Your answer