Hello. Is there a way for me to automatically put the quantity on the summary sheet based on the cell value? I tried to use the countif function in the summary sheet but it didn't work the way I wanted it to be. Thank you in advance.

Looking for formula to sum values in column C. The formula setup should include column A and B to correctly sum the values into column D, desired column. See sample file. Thanks

hi all,

I am trying to create a visual "percent savings" pricing tool that will also show when there is a loss. I have my % change formula in there, but from an optics perspective i am trying to show when there is a savings versus a loss, but because the way percentages are calculated, I cant conditionally format an absolute value to show as a negative when there is a loss. I hope that makes sense? in otherwords, i am trying to flip the positive and negative values.

Capture.PNG

]]>I am trying to create a visual "percent savings" pricing tool that will also show when there is a loss. I have my % change formula in there, but from an optics perspective i am trying to show when there is a savings versus a loss, but because the way percentages are calculated, I cant conditionally format an absolute value to show as a negative when there is a loss. I hope that makes sense? in otherwords, i am trying to flip the positive and negative values.

Capture.PNG

Hi,

I have the column from the screen 1 below.

__Screen 1:__

Capture.PNG

this column has the formula as below.

(=IFERROR(IF(VLOOKUP(A6,Sales!A:B,2,0)="","",IFERROR(VLOOKUP(A6,Sales!A:B,2,0),"")),""))

I want to highlight the empty cell and have tried to create the rule with format only cells that contain blank (screen 12 but it didn't work.

__screen 2:__

Capture1.PNG

I think probably because the column contain the formula in it?

Thanks

]]>I have the column from the screen 1 below.

Capture.PNG

this column has the formula as below.

(=IFERROR(IF(VLOOKUP(A6,Sales!A:B,2,0)="","",IFERROR(VLOOKUP(A6,Sales!A:B,2,0),"")),""))

I want to highlight the empty cell and have tried to create the rule with format only cells that contain blank (screen 12 but it didn't work.

Capture1.PNG

I think probably because the column contain the formula in it?

Thanks

I want the results of a formula to display only if text is input into three different cells. At the moment it displays results if text is input into the last cell.

C16:E16 are cells that require input and therefore Excel should ensure each has data rather than checking only the last cell.

For example, the formula in one of the cells afterwards is

. What would be required to make it validate as described? I presume it's not as easy as

?

How about validating data on several rows with ranges?

]]>C16:E16 are cells that require input and therefore Excel should ensure each has data rather than checking only the last cell.

For example, the formula in one of the cells afterwards is

Code:

`=IF(ISBLANK(E16),"",E18-C22)`

Code:

`=IF(ISBLANK(C16:E16),"",E18-C22)`

How about validating data on several rows with ranges?

Hello there, I have a form that requires dates to be input and some users will try entering in the format 01042020, others as 010420, others as 01.04.2020, others as 01.04.20, others as 01/04/2020 and others as 01/04/20. Is there a way to enable users to input in each of these ways and for Excel to recognise the date correctly and convert it automatically to appear as 01/04/2020?

I already have the cell format as Date dd/mm/yyyy.

Thank you.

]]>I already have the cell format as Date dd/mm/yyyy.

Thank you.

Hi. Im currently facing an issue with applying a formula to get the correct value in column R and in the attached screenshot will be applicable to cell R16

Please note - The rest of the functions are working fine for the remaining datasheet and the resulting formula we choose for column R will also change the first function mentioned below that is currently being used for data range K14:R30

EXAMPLE - The data in cell I14 is as follows JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN

The following formula is used in cell Q14 =(LEN(I14)-LEN(SUBSTITUTE(I14,"JFVP","")))/LEN("JFVP") to return the correct value 12.

Similarly all the data in range K14:R30 is compiled

Proceeding with the same example, the following function is applied to cell J6 =SUMPRODUCT(--($J$14:$J$36=Q$14:Q$36),--(Q$14:Q$36=12))

Now, The only issue i am facing is in cases where the data in column I is slightly different.

EXAMPLE - the data i cell I16 is as follows JFTW06-MFN,JFTW06-MFN,JFGP06-MFN,JFTW06-MFN,JFTW06-MFN,JFGP06-MFN,JFTW06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFTW06-MFN

Here there are two different values - JFTW06-MFN and JFGP06-MFN and hence values of 6 are shown in cell K16 and L16 respectively. But the correct value should be 12 in cell R16 which should ultimately lead to an addition in cell J6

I hope the above mentioned information was not too confusing as I'm not an expert in this field at all but your advice would go a long long way. Thank You!

Please note - The rest of the functions are working fine for the remaining datasheet and the resulting formula we choose for column R will also change the first function mentioned below that is currently being used for data range K14:R30

EXAMPLE - The data in cell I14 is as follows JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN

The following formula is used in cell Q14 =(LEN(I14)-LEN(SUBSTITUTE(I14,"JFVP","")))/LEN("JFVP") to return the correct value 12.

Similarly all the data in range K14:R30 is compiled

Proceeding with the same example, the following function is applied to cell J6 =SUMPRODUCT(--($J$14:$J$36=Q$14:Q$36),--(Q$14:Q$36=12))

Now, The only issue i am facing is in cases where the data in column I is slightly different.

EXAMPLE - the data i cell I16 is as follows JFTW06-MFN,JFTW06-MFN,JFGP06-MFN,JFTW06-MFN,JFTW06-MFN,JFGP06-MFN,JFTW06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFTW06-MFN

Here there are two different values - JFTW06-MFN and JFGP06-MFN and hence values of 6 are shown in cell K16 and L16 respectively. But the correct value should be 12 in cell R16 which should ultimately lead to an addition in cell J6

I hope the above mentioned information was not too confusing as I'm not an expert in this field at all but your advice would go a long long way. Thank You!

Hi Everyone,

I would like to know the difference between 2 dates in Years - Mounths - days - Hours, but I would like the result with 2 decimals... ("M-N-O-P" / "8")

I try with the formula "DATADIF"... but doesn't work !

Thanks in advance

I would like to know the difference between 2 dates in Years - Mounths - days - Hours, but I would like the result with 2 decimals... ("M-N-O-P" / "8")

I try with the formula "DATADIF"... but doesn't work !

Thanks in advance

I am trying to count the number of time "Day" occurs in column 'D' based upon the previous 5 work days (not including weekends)

I need column 'A' to produce that number ... 1, 2, 3, etc.

I need column 'A' to produce that number ... 1, 2, 3, etc.

Hi,

I have a sheet which has a cluster of values in Col B. For eg: In Col B, for the first cluster (rows 2 and 3 of Col B), if the corresponding rows in Col A match (that is row 1 and row 2 of col A are similar), then i need an output X. If for the cluster in Col B, the corresponding rows in Col A do not match, then i need output Y in Col C. In this case rows 1 and 2 in Col A do not match, hence output is Y. However the rows in green satisfy my X (match) condition. You can see for Cluster values Test_1169 in Col B, the col A values all match each other which is 513548, hence an X in Col C. For every other row other than this, there is no match (marked in orange) and hence the output is Y. Which formula will help me automate this?

I have a sheet which has a cluster of values in Col B. For eg: In Col B, for the first cluster (rows 2 and 3 of Col B), if the corresponding rows in Col A match (that is row 1 and row 2 of col A are similar), then i need an output X. If for the cluster in Col B, the corresponding rows in Col A do not match, then i need output Y in Col C. In this case rows 1 and 2 in Col A do not match, hence output is Y. However the rows in green satisfy my X (match) condition. You can see for Cluster values Test_1169 in Col B, the col A values all match each other which is 513548, hence an X in Col C. For every other row other than this, there is no match (marked in orange) and hence the output is Y. Which formula will help me automate this?

I want to have times highlighted when a certain threshold is reached.

I am unable to accomplish this. Any assistance would be greatly appreciated.

In the attached example if 00:15:00 minutes are exceed.

I am unable to accomplish this. Any assistance would be greatly appreciated.

In the attached example if 00:15:00 minutes are exceed.

Hi, here is the problem....

I have a potential solution that starts in column N2 which is 12/7/18 on my sheet.

Starting on 12/10/18, I need a formula that checks column N2 and if N2 is between the Hi (b3) / Lo (c3) , then J3 should list the solution from N2, and k3 should list the solution from O2.

The following day, the formula should check the data from n2, and n3, and so on as the file gets larger. I have attached a copy of the spreadsheet. I have tried vlookup and index, but my formula knowledge is limited, so any help would be greatly appreciated.

Matrix

I have a potential solution that starts in column N2 which is 12/7/18 on my sheet.

Starting on 12/10/18, I need a formula that checks column N2 and if N2 is between the Hi (b3) / Lo (c3) , then J3 should list the solution from N2, and k3 should list the solution from O2.

The following day, the formula should check the data from n2, and n3, and so on as the file gets larger. I have attached a copy of the spreadsheet. I have tried vlookup and index, but my formula knowledge is limited, so any help would be greatly appreciated.

Matrix

Hi Experts!!

Could you please help me at the attached file. I want to pull data Final-1 to Final list. Only non zero value as well the shops and items.

Could you please help me at the attached file. I want to pull data Final-1 to Final list. Only non zero value as well the shops and items.

I have a report that pulls a 6 month date range worth of data.

This pulls the date range in numbers like 43913 and 43919.

I need to break this data down into 1 week pieces and am trying to create headings automatically that will look like

3/23/2020 - 3/29/2020

I am trying to use a formula like this:

=CONCAT((MIN('Data 6 mo sales'!H:H)),"-",(MIN('Data 6 mo sales'!H:H)+6))

This returns 43913-43919.

How can I convert those to dates?

Thanks,

Anthony

]]>This pulls the date range in numbers like 43913 and 43919.

I need to break this data down into 1 week pieces and am trying to create headings automatically that will look like

3/23/2020 - 3/29/2020

I am trying to use a formula like this:

=CONCAT((MIN('Data 6 mo sales'!H:H)),"-",(MIN('Data 6 mo sales'!H:H)+6))

This returns 43913-43919.

How can I convert those to dates?

Thanks,

Anthony

I'm trying to list out a the Unique values in a range in Alphabetical, Ascending Order.

I came across this how to for the formula on the web here: hXXps://exceljet.net/formula/extract-unique-items-from-a-list

And I am using the non-array version of if like this.

And the above does list out the Unique values in my named range. But is it listing them in descending Z - A order. Does anyone know how to flip that to be Ascending?

]]>I came across this how to for the formula on the web here: hXXps://exceljet.net/formula/extract-unique-items-from-a-list

And I am using the non-array version of if like this.

Code:

`=IFERROR(LOOKUP(2,1/(COUNTIF($A$3:A3,MyNamedRange)=0),MyNamedRange),"N/A")`