Hi. I have the below formula but it returns FALSE if it it's false.

Can anybody advise how to read of it please?

=IF(C8="","",IF(AND(K8<>"",O8=""),"Needs Pricing",IF(AND(K8="",O8<>""),"Needs Quote",IF(AND(K8="",O8=""),"NoQuote NoPrice",IF(AND(K8<>"",O8<>""),"Quoted&Priced")))))

]]>Can anybody advise how to read of it please?

=IF(C8="","",IF(AND(K8<>"",O8=""),"Needs Pricing",IF(AND(K8="",O8<>""),"Needs Quote",IF(AND(K8="",O8=""),"NoQuote NoPrice",IF(AND(K8<>"",O8<>""),"Quoted&Priced")))))

Hi Everyone

Wonder if you can help with a formula. Basically needs to total the amount of "In Plan" that are due to end in the months H3-J3. As you can see some of the End Dates have been Rescheduled with another column showing this so need something to only take the latest end date. I have tried incorporating a MAX but only get errors

Below is where I got to but have also attached a workbook to make things easier

=SUMIFS(B:B,"Permenant",C:C,"In Plan"&MAX(D:E)&H2)

I am not precious over the formula so if it can be done in another way I would be happy to hear it!

Many thanks for your help!

Jamie

Wonder if you can help with a formula. Basically needs to total the amount of "In Plan" that are due to end in the months H3-J3. As you can see some of the End Dates have been Rescheduled with another column showing this so need something to only take the latest end date. I have tried incorporating a MAX but only get errors

Below is where I got to but have also attached a workbook to make things easier

=SUMIFS(B:B,"Permenant",C:C,"In Plan"&MAX(D:E)&H2)

I am not precious over the formula so if it can be done in another way I would be happy to hear it!

Many thanks for your help!

Jamie

I would like to do the following with measuring data:

1) Each row contains a set of data

2) For each row of data, I want to make a reference to a range of other rows

3) For each row of data, I want to calculate the average over this associated range of other rows

4) So far I can manage this, but I would like to make the formula "spill" over the entire target array, so I don't have to worry about updating formulas when rows of data are inserted in the middle etc.

E.g., this could be a set of measuring data with multiple zero values taken on several instances, where I want to specify which range of zero values that should be associated to each row, but I have other applications as well (all for interpolating).

The example Excel sheet shows the four calculating approaches I've tried so far, where every attempt to make the formula spill, using ranges like B2:B19 or G2# as parameters, has failed.

(Note that the example sheet has different row referrals, because I wanted a little more data for the example calculations.)

All my other columns in the calculations rely on "spill" so it could be a risk if one column suddenly needs manual updating. I also tried to find appropriate Control-Shift-Enter (CSE) formulas instead, but ran into the same problems there.

1) Each row contains a set of data

2) For each row of data, I want to make a reference to a range of other rows

3) For each row of data, I want to calculate the average over this associated range of other rows

4) So far I can manage this, but I would like to make the formula "spill" over the entire target array, so I don't have to worry about updating formulas when rows of data are inserted in the middle etc.

E.g., this could be a set of measuring data with multiple zero values taken on several instances, where I want to specify which range of zero values that should be associated to each row, but I have other applications as well (all for interpolating).

Value | 1st pr 0 | Last pr 0 | 1st suc 0 | last suc 0 | <pr 0> | <suc 0> | zero | corrected |

0 | 1 | 2 | 7 | 8 | 0,05 | 0,25 | 0,03 | -0,03 |

0,1 | 1 | 2 | 7 | 8 | 0,05 | 0,25 | 0,07 | 0,03 |

5,0 | 1 | 2 | 7 | 8 | 0,05 | 0,25 | 0,10 | 4,90 |

5,1 | 1 | 2 | 7 | 8 | 0,05 | 0,25 | 0,13 | 4,97 |

10,2 | 1 | 2 | 7 | 8 | 0,05 | 0,25 | 0,17 | 10,03 |

10,2 | 1 | 2 | 7 | 8 | 0,05 | 0,25 | 0,20 | 10,00 |

0,2 | 1 | 2 | 7 | 8 | 0,05 | 0,25 | 0,23 | -0,03 |

0,3 | 1 | 2 | 7 | 8 | 0,05 | 0,25 | 0,27 | 0,03 |

0,0 | 9 | 10 | ... | ... | ... | ... | ... | ... |

0,0 | 9 | 10 | ... | ... | ... | ... | ... | ... |

The example Excel sheet shows the four calculating approaches I've tried so far, where every attempt to make the formula spill, using ranges like B2:B19 or G2# as parameters, has failed.

(Note that the example sheet has different row referrals, because I wanted a little more data for the example calculations.)

All my other columns in the calculations rely on "spill" so it could be a risk if one column suddenly needs manual updating. I also tried to find appropriate Control-Shift-Enter (CSE) formulas instead, but ran into the same problems there.

Hi to all,

if C4 is present in sheet "articoli" column B write in F5

"article present in articoli sheet in pos. A-02 "

if C4 is not present in sheet "articoli" column B write in F5

"article not present in articoli sheet "

I hope I have explained

I attach example

thanks

john

if C4 is present in sheet "articoli" column B write in F5

"article present in articoli sheet in pos. A-02 "

if C4 is not present in sheet "articoli" column B write in F5

"article not present in articoli sheet "

I hope I have explained

I attach example

thanks

john

I have 2 sheets. Sheet 2 is the master data and I want to pull all data in column A to Sheet 1. But I only want to do this if Sheet 1, J8 is equal to "All" ('All' is in a list). Any help would be appreciated.

]]>hello Forum,

Greetings from Cebu, Philippines

Here is my question, and I do post a pdf illustration to support my question

I have two columns of 12 hard core figures

Col D are percentages; Col C are months numbers (from 1 to 12)

I try to set up a function that will result in the following:

I want as a result of the function the lowest month number in col C array matching any percentage in Col D array that is superior to zero.

Not sure my question is clear, but I think the attachment shall clarify the question

Thank you all

Greetings from Cebu, Philippines

Here is my question, and I do post a pdf illustration to support my question

I have two columns of 12 hard core figures

Col D are percentages; Col C are months numbers (from 1 to 12)

I try to set up a function that will result in the following:

I want as a result of the function the lowest month number in col C array matching any percentage in Col D array that is superior to zero.

Not sure my question is clear, but I think the attachment shall clarify the question

Thank you all

Hi there I am at a loss and would so appreciate any help.

I have a formula calculating NETWORKDAYS between 2 dates. If the 2 dates are equal I need it to result in zero, instead it will result with a "1". I tried fixing this using a -1 at the end, but this won't work if the second date is less than the first date, where I need a result of -1, or -2, -3 etc.

Part of my formula also contains an IF formula to only look at the date cells if there is an actual date in the cell.

So I think I need to combine 2 IF formulas?

My formula currently is: =IF(AND(ISNUMBER(K135),ISNUMBER(M135)),NETWORKDAYS(K135,M135,Lookups!V32:V59)," ")

Now, I need to subtract 1 if the value is positive, and add one if the value is negative. Please help!!

]]>I have a formula calculating NETWORKDAYS between 2 dates. If the 2 dates are equal I need it to result in zero, instead it will result with a "1". I tried fixing this using a -1 at the end, but this won't work if the second date is less than the first date, where I need a result of -1, or -2, -3 etc.

Part of my formula also contains an IF formula to only look at the date cells if there is an actual date in the cell.

So I think I need to combine 2 IF formulas?

My formula currently is: =IF(AND(ISNUMBER(K135),ISNUMBER(M135)),NETWORKDAYS(K135,M135,Lookups!V32:V59)," ")

Now, I need to subtract 1 if the value is positive, and add one if the value is negative. Please help!!

Hi Experts!

I'm trying to do a lookup with multiple criteria, but instead of trying to find 2 exact values, one is a specific code (exact match) and the other is a date to be matched within a date range in 2 different columns.

I'm including an excel file with an example of the 2 sets of data: A) with the code, specific date, and B) with the code, date range and price to be returned into the first chart.

Hope the file helps to understand my question better. I have been trying to do it with INDEX and MATCH formulas but I have not even come close to a solution yet.

Set A

Item Code Date Desired result (currently empty :()

12345678 2/15/2009 $10

12345678 7/2/2009 $9

87654321 11/12/2009 $12

87654321 1/1/2010 Not found (N/A)

87654321 5/1/2009 $19

87654321 8/3/2009 $21

87654321 10/1/2009 $23

87654321 9/30/2009 $21

12348765 2/1/2009 $5

12348765 11/12/2009 $3

12348765 10/1/2009 $3

12348765 10/1/2009 $3

Set B

Item Code Valid From Valid To Price

12345678 1/1/2009 3/1/2009 $10

12345678 4/1/2009 6/30/2009 $11

12345678 7/1/2009 9/30/2009 $9

12345678 10/1/2009 12/31/2009 $12

87654321 1/1/2009 3/1/2009 $20

87654321 4/1/2009 6/30/2009 $19

87654321 7/1/2009 9/30/2009 $21

87654321 10/1/2009 12/31/2009 $23

12348765 1/1/2009 3/1/2009 $5

12348765 4/1/2009 6/30/2009 $4

12348765 7/1/2009 9/30/2009 $6

12348765 10/1/2009 12/31/2009 $3

Thanks in advance!!

I'm trying to do a lookup with multiple criteria, but instead of trying to find 2 exact values, one is a specific code (exact match) and the other is a date to be matched within a date range in 2 different columns.

I'm including an excel file with an example of the 2 sets of data: A) with the code, specific date, and B) with the code, date range and price to be returned into the first chart.

Hope the file helps to understand my question better. I have been trying to do it with INDEX and MATCH formulas but I have not even come close to a solution yet.

Set A

Item Code Date Desired result (currently empty :()

12345678 2/15/2009 $10

12345678 7/2/2009 $9

87654321 11/12/2009 $12

87654321 1/1/2010 Not found (N/A)

87654321 5/1/2009 $19

87654321 8/3/2009 $21

87654321 10/1/2009 $23

87654321 9/30/2009 $21

12348765 2/1/2009 $5

12348765 11/12/2009 $3

12348765 10/1/2009 $3

12348765 10/1/2009 $3

Set B

Item Code Valid From Valid To Price

12345678 1/1/2009 3/1/2009 $10

12345678 4/1/2009 6/30/2009 $11

12345678 7/1/2009 9/30/2009 $9

12345678 10/1/2009 12/31/2009 $12

87654321 1/1/2009 3/1/2009 $20

87654321 4/1/2009 6/30/2009 $19

87654321 7/1/2009 9/30/2009 $21

87654321 10/1/2009 12/31/2009 $23

12348765 1/1/2009 3/1/2009 $5

12348765 4/1/2009 6/30/2009 $4

12348765 7/1/2009 9/30/2009 $6

12348765 10/1/2009 12/31/2009 $3

Thanks in advance!!

I have a spreadsheet with a formula in Cell D70 =ROUNDUP(((E42/0.3)*D42),0)

I would like for cell D70 to also incorporate cells E43 and D43, and also cells E44 and D44

Can someone help with a simple formula please

]]>I would like for cell D70 to also incorporate cells E43 and D43, and also cells E44 and D44

Can someone help with a simple formula please

Hello,

I would like to apply conditional formatting based on the value of a cell above those that need to be formatted, and then stop when the next set of rows is to be evaluated. For instance:

Column A

row 1: value here is less than 30, so:

row 2: red font

row 3: red font

row 4: value here is more than 30, so:

row 5: normal font

row 6: normal font

row 7: normal font

row 8: normal font

row 9: value here is .......etc.

The number of rows between the evaluation rows is not consistent.

A sample worksheet is attached.

Many thanks for any help!

I would like to apply conditional formatting based on the value of a cell above those that need to be formatted, and then stop when the next set of rows is to be evaluated. For instance:

Column A

row 1: value here is less than 30, so:

row 2: red font

row 3: red font

row 4: value here is more than 30, so:

row 5: normal font

row 6: normal font

row 7: normal font

row 8: normal font

row 9: value here is .......etc.

The number of rows between the evaluation rows is not consistent.

A sample worksheet is attached.

Many thanks for any help!

Hi Community,

I'm using the following formula to work out how many working days there are in the month.

=NETWORKDAYS(A2,EOMONTH(A2,0),Holidays[Date])

We have 3 public holidays here in NZ in Jan, but for some reason, the formula I'm using is not picking up the data I've designated for holidays.

It would be a great help if someone can spot where I'm going wrong!

Book Holidays.xlsx

Obviously, as the month changes the public holidays should be taken into account

Thanks

Tony

]]>I'm using the following formula to work out how many working days there are in the month.

=NETWORKDAYS(A2,EOMONTH(A2,0),Holidays[Date])

We have 3 public holidays here in NZ in Jan, but for some reason, the formula I'm using is not picking up the data I've designated for holidays.

It would be a great help if someone can spot where I'm going wrong!

Book Holidays.xlsx

Obviously, as the month changes the public holidays should be taken into account

Thanks

Tony

Hi everyone,

Apologies in advance if this is a silly question, but i cant seem to get this IF statement to work.

Example

I have 4 columns, A-D presented below:

A B C D

-30 , -30, 0, 10

i would like to replace replace the numbers with words via a IF statement.

for example on column B:

If column B is greater than Column A than return "Improvement"

but if

If column B is greater than Column A and is greater than "0" then return "Recovered"

but if

Column B is equal to Column A then return "No Change"

Is this possible to generate into a IFstatment/ formula please?

Thanks in advance everyone :)

CT

]]>Apologies in advance if this is a silly question, but i cant seem to get this IF statement to work.

Example

I have 4 columns, A-D presented below:

A B C D

-30 , -30, 0, 10

i would like to replace replace the numbers with words via a IF statement.

for example on column B:

If column B is greater than Column A than return "Improvement"

but if

If column B is greater than Column A and is greater than "0" then return "Recovered"

but if

Column B is equal to Column A then return "No Change"

Is this possible to generate into a IFstatment/ formula please?

Thanks in advance everyone :)

CT

Does anyone know how I can get these kinds of formulas to not return any error values (like #VALUE! or #DIV/0!) or any zero values and instead only return rows that have actual non-zero numbers in the 7th column?

=INDEX(SORT('Sheet1'!A3:P356,7,-1),SEQUENCE(10),SEQUENCE(1,9))

=INDEX(SORT('Sheet1'!A3:P356,7,1),SEQUENCE(10),SEQUENCE(1,9))

Thank you very much in advance!

]]>=INDEX(SORT('Sheet1'!A3:P356,7,-1),SEQUENCE(10),SEQUENCE(1,9))

=INDEX(SORT('Sheet1'!A3:P356,7,1),SEQUENCE(10),SEQUENCE(1,9))

Thank you very much in advance!

Hello Community,

I've have an issue and I'm assuming I'd need to use the INDEX / SORT / MATCH functions, but I'm a bit perplexed on where to start with this one - This one is beyond my tiny little brain :)

I have two tables.

Both tables have the same format, and Table1 will 'always' include the data that's in Table2.

Table1 will also have more data than Table2.

So I need to compare the data int the two tables and the data thats in Table1 & not in table 2 be listed in a separate list.

See an example of the data and how ideally it would get displayed.

Any Help greatly appreciated :)

Lists.xlsx

Thanks

Tony

]]>I've have an issue and I'm assuming I'd need to use the INDEX / SORT / MATCH functions, but I'm a bit perplexed on where to start with this one - This one is beyond my tiny little brain :)

I have two tables.

Both tables have the same format, and Table1 will 'always' include the data that's in Table2.

Table1 will also have more data than Table2.

So I need to compare the data int the two tables and the data thats in Table1 & not in table 2 be listed in a separate list.

See an example of the data and how ideally it would get displayed.

Any Help greatly appreciated :)

Lists.xlsx

Thanks

Tony

Hi, I need help on finding the correct formula for the following: :confused:

Here is what I'm looking for:

If Sheet2 column C corresponds to Sheet1 column A then apply amount of Sheet2 column B in Sheet1 column B

Example: 'Sheet2' C2 and C3 match 'Sheet1' A2 -> so I want their amount ('Sheet2' B2 and B3) to be added in 'Sheet1' B2.

Can someone help with the correct formula, please?

Thank you so much

Here is what I'm looking for:

If Sheet2 column C corresponds to Sheet1 column A then apply amount of Sheet2 column B in Sheet1 column B

Example: 'Sheet2' C2 and C3 match 'Sheet1' A2 -> so I want their amount ('Sheet2' B2 and B3) to be added in 'Sheet1' B2.

Can someone help with the correct formula, please?

Thank you so much