***Official*** Excel Help Corner (1 Viewer)

[Ned]

Just out of curiosity:

I have a pair of cells with simple yes/no drop downs. Elsewhere on the sheet, I have three cells with yes/no drop downs. The thing is, in practical use, these sets are mutually exclusive; that is, if you answer YES to either or both of the first two drop downs, all three in the second set should be NO. Conversely, if any one, two, or three cells in set two are YES, then both of the cells in the first section should be NOs.

Since I'm using data validation to create the drop downs in the first place, is there any way to put a second validation to ensure that the user of the sheet hasn't accidentally put a YES in both sections?

Right now I just have an IF(AND(OR())) nest of formulas to spit out an "ERROR!" message, but I was wondering if it's possible to force the user to use exclusive values.

(I see a way to prevent the same option being used twice, for example, in consecutive drop downs, but that doesn't quite get me where I need to be since valid entries might have one, two, or up to three YES's)

I'm not exactly following you, but I think what you're looking for is using the Custom option on the Data Validation.

Check this out

 

[Ned]

Is there a way to set permissions on a spreadsheet that will allow people to input data but not change the structure of the sheet or edit formulas?

Yeah you can lock all cells except the ones you want them to enter data.  All cells come with the option set to Lock them, but they won't be locked until you choose to protect the sheet.  So you need to turn off the lock feature for the few cells you want the user to edit before locking down the sheet.

Highlight cells you want them to input data, right click and choose Format Cells.  Go to Protection tab and uncheck the Lock.  Go to View ribbon and choose protect sheet.  In there are a bunch of options to choose from which will lock everything in your sheet except the cells you just unlocked.

 

[Nick Vermeil]

Yeah you can lock all cells except the ones you want them to enter data.  All cells come with the option set to Lock them, but they won't be locked until you choose to protect the sheet.  So you need to turn off the lock feature for the few cells you want the user to edit before locking down the sheet.

Highlight cells you want them to input data, right click and choose Format Cells.  Go to Protection tab and uncheck the Lock.  Go to View ribbon and choose protect sheet.  In there are a bunch of options to choose from which will lock everything in your sheet except the cells you just unlocked.

Thanks!

 

[Ned]

Almost... not sure if I can figure out the second step I need to do.

A1 and A2 can be either of yes or no, so I'm using data validation to create the drop down selection.

ONLY IF A1 and A2 are BOTH no do I want the user allowed to select YES in the drop downs for B1, B2, and B3. Otherwise, if either is YES, only NO should be able to be selected. 

I think what i want is a version without duplicates, but, allowing duplicates in the A's and B's internally, just not in the other. I know, hard to explain.

Check this one out instead

 

[acarey50]

Pretty sure the answer is no, but, figured I might as well ask the experts:

Is there a way to exclude a visible row from the Print Area, while keeping rows above and below it printing? Without hiding it. That is, I want to see a row across the middle of the range when I'm working in the spreadsheet, but, when I actually print it I want it to not print that row. 

I was just looking into something similar - the best method I could find was to set up a custom view that I could toggle between to hide the rows that I didn't want to print when printing, but show when I was working in the file.

Microsoft Article on Process   = This has the directions on setting up the custom view. Essentially hide the rows you don't want to print, set it as a custom view, then all you have to do in future is apply that view before printing, then go back to the default view to work on the sheet.

Hope that helps.

 

[Ned]

There is also a way to select two, non-continuous Print Areas, but, they only print out on separate sheets of paper. Which doesn't help. Oh well. I'm prepared to live without it.

(I designed a spreadsheet that other people in my department use. I have a few cells outside the print area to warn them if custom settings are used, so that my coworkers hopefully notice and change without just assuming it'll work automagically. I'd love those warning cells to be more prominently placed instead of keeping them outside the print area.)

Could you use conditional formatting to alert them?

 

[bryhamm]

There is also a way to select two, non-continuous Print Areas, but, they only print out on separate sheets of paper. Which doesn't help. Oh well. I'm prepared to live without it.

(I designed a spreadsheet that other people in my department use. I have a few cells outside the print area to warn them if custom settings are used, so that my coworkers hopefully notice and change without just assuming it'll work automagically. I'd love those warning cells to be more prominently placed instead of keeping them outside the print area.)

Is it just 1 sheet when you print, or more than 1?

 

[Ned]

I do. But when there's no alert I don't want the cells/row to appear in the printout as a blank space in the middle of the table. 

It's difficult to offer help without seeing the setup, so I'm sorry if some of these ideas are dumb.

I know you said you don't want to hide the row(s), but you could add a macro to the sheet (or workbook) to auto-hide based on the criteria you're using to add the warnings.

 

[bryhamm]

I want one sheet. If you select non continuous print areas, each get their own. 

It's an ugly way to do it, but you could make the rows in your top half you want the "rows to repeat at the top" in you page setup for printing.  Then, make the print area the rows you want at the bottom.

 

[bryhamm]

the other thing you could do instead of actually hiding the rows is to group the rows (Data; Group).  It will put a toggle box on the left side that can be quickly open and closed.  Much easier than hiding and unhiding rows.

 

[acarey50]

I do. But when there's no alert I don't want the cells/row to appear in the printout as a blank space in the middle of the table. 

It'd be getting a little passed by ability to actually help, but could you put in some VBA to trigger a pop up window when the conditions requiring the alert are met?

 

[cubd8]

I have a formula (below) that is attempting to calculate the number of days between two dates, minus weekends.

It is working, but I would like to make some adjustments. 

=IF(OR(Z2<AS2,AS2="",Z2=""),"N/A",NETWORKDAYS(AS2,Z2))

I would like this result to have a value '0' as opposed to Day 0 having a value of 1 when the days are weekdays.

Is there a way to update the above formula to result in the below examples have a value of '0' (instead of '1').

Column Z                                      Column AS                          Column AT

1/16/2019                                        1/16/2019                                     1

4/16/2019                                        4/16/2019                                     1

5/16/2019                                        5/16/2019                                     1

 

[Ned]

I have a formula (below) that is attempting to calculate the number of days between two dates, minus weekends.

It is working, but I would like to make some adjustments. 

=IF(OR(Z2<AS2,AS2="",Z2=""),"N/A",NETWORKDAYS(AS2,Z2))

I would like this result to have a value '0' as opposed to Day 0 having a value of 1 when the days are weekdays.

Is there a way to update the above formula to result in the below examples have a value of '0' (instead of '1').

Column Z                                      Column AS                          Column AT

1/16/2019                                        1/16/2019                                     1

4/16/2019                                        4/16/2019                                     1

5/16/2019                                        5/16/2019                                     1

I think I'm missing something here, but why not just subtract 1?

=IF(OR(Z2<AS2,AS2="",Z2=""),"N/A",NETWORKDAYS(AS2,Z2))-1

 

[cubd8]

I think I'm missing something here, but why not just subtract 1?

=IF(OR(Z2<AS2,AS2="",Z2=""),"N/A",NETWORKDAYS(AS2,Z2))-1

When I add a "-1", I get some unexpected results. 

Basically when both dates are on the weekend, the expected result is a 0, but now is a -1 and the #VALUE! error as well for blank values.

Column Z                                      Column AS                          Column AT

___________                                      _________                                    #VALUE! (should be defaulting to a N/A when one of the date fields is blank)

4/16/2019                                        4/16/2019                                     0 (was a '1' prior, but the subtraction correctly updates it to a '0')

3/16/2019                                        3/16/2019                                     1 (was a '-0' prior, but now is a '-1')

 

[bryhamm]

I have a formula (below) that is attempting to calculate the number of days between two dates, minus weekends.

It is working, but I would like to make some adjustments. 

=IF(OR(Z2<AS2,AS2="",Z2=""),"N/A",NETWORKDAYS(AS2,Z2))

I would like this result to have a value '0' as opposed to Day 0 having a value of 1 when the days are weekdays.

Is there a way to update the above formula to result in the below examples have a value of '0' (instead of '1').

Column Z                                      Column AS                          Column AT

1/16/2019                                        1/16/2019                                     1

4/16/2019                                        4/16/2019                                     1

5/16/2019                                        5/16/2019                                     1

I'm a little confused on what it is you are trying to do.  If both days are the same day and are both weekdays, you want a 0?  So, if you put in the formula to subtract a 1 it works for these 3 but when does it mess things up?

 

[bryhamm]

if you have 5/16/19 and 5/15/19 are you expecting 2 or 1?

 

[Ned]

I think I'm missing something here, but why not just subtract 1?

=IF(OR(Z2<AS2,AS2="",Z2=""),"N/A",NETWORKDAYS(AS2,Z2))-1

When I add a "-1", I get some unexpected results. 

Basically when both dates are on the weekend, the expected result is a 0, but now is a -1 and the #VALUE! error as well for blank values.

Column Z                                      Column AS                          Column AT

___________                                      _________                                    #VALUE! (should be defaulting to a N/A when one of the date fields is blank)

4/16/2019                                        4/16/2019                                     0 (was a '1' prior, but the subtraction correctly updates it to a '0')

3/16/2019                                        3/16/2019                                     1 (was a '-0' prior, but now is a '-1')

OK, this will do it.

=IF(OR(Z2<AS2,AS2="",Z2=""),"N/A",IFERROR(NETWORKDAYS(AS2,Z2)-1,"N/A"))

 

[Tiger Fan]

Having problems with some nested if statements when determining wins/losses in gambling.  I know I have each individual if statement correct b/c I've tried them all independently, but when combine them (based upon whether the bet is an over or under) it comes up false for the overs.  I'm sure there's a simple solution, but it's driving me nuts.

Thanks

Note column S (highlighted)

https://docs.google.com/spreadsheets/d/1i3Z52x5VzMAg55xMKYu6-nv4sxPvSaL2PP6T5npjEDE/edit?usp=sharing

 

[Tiger Fan]

=IF(C7="under",IF(R7<D7,"w",IF(R7>D7,"l",IF(C7="over",if(R7>D7,"w",IF(R7<D7,"l"))))))

The bolded is in the wrong place I think. You don't need to further evaluate another clause. Just "if C7 is 'Under', do this, otherwise, do that". 

Try: =IF(C7="under",IF(R7<D7,"w","l"),IF(R7>D7,"w","l"))

That'll just look at C7 and see if it says "under". If it does, it looks to see if R7<D7, and if it is, give you a W, otherwise an L. If C7 doesn't say "under", it does the opposite, and checks if R7>D7, and gives you a W if it is and an L if it isn't.

Thank you...figured it was something simple.  Appreciate it!

Might be more to come as I flesh this out...

 

[Gottabesweet]

Basic excel question for someone that doesn’t use it much. 
 

I have 30 names. 
I want them each matched up to a number 1-30 (all different)

i want to randomize this every time I click (Depending on dice roll will determine how many times I click to randomize pairings.

 

[Ned]

Basic excel question for someone that doesn’t use it much. 
 

I have 30 names. 
I want them each matched up to a number 1-30 (all different)

i want to randomize this every time I click (Depending on dice roll will determine how many times I click to randomize pairings.

Give this a read. 
 

https://trumpexcel.com/generate-unique-random-numbers-in-excel/

 

[TheIronSheik]

This is an odd one but figured I'd ask.  I thought I saw it done before by someone (maybe on here) with something like stocks or something.

So we run a golf pool for the majors.  I have an elaborate Excel spreadsheet that shows everyone's golfers and scores.  Every golfer picks 6 guys and you add up the 5 best golfers scores to see who has the lowest score.  That's just to give an idea of what's all happening in this spreadsheet.  Anyway, I have to open up the CBS Sports web page and I copy the scores off the page, then paste them into a tab on the sheet.  Once copied in, the spreadsheet and formulas do all of the work. So to update the scores for everyone, I just have to copy in the scores from the web page.  

Is there a way to have Excel automatically pull that info from the web page maybe each time the sheet is opened?  That way I could make it a online doc and people could check the scores whenever they wanted?  Right now, I send out updates every hour during the rounds.  I've tried to send the spreadsheet to people and give them instructions on how to copy the data in, but for some reason they all think it's too complicated.  That baffles me, but whatever.  Just thought if it could pull data automatically, it would be a godsend.  

Side note about my ability:  I'm about a 8.5 to 9 in Excel, but not including Macros.  So if it includes using Macros, you might have to dumb down the instructions a little for me. 

TIA
TIS

 

[Ned]

This is an odd one but figured I'd ask.  I thought I saw it done before by someone (maybe on here) with something like stocks or something.

So we run a golf pool for the majors.  I have an elaborate Excel spreadsheet that shows everyone's golfers and scores.  Every golfer picks 6 guys and you add up the 5 best golfers scores to see who has the lowest score.  That's just to give an idea of what's all happening in this spreadsheet.  Anyway, I have to open up the CBS Sports web page and I copy the scores off the page, then paste them into a tab on the sheet.  Once copied in, the spreadsheet and formulas do all of the work. So to update the scores for everyone, I just have to copy in the scores from the web page.  

Is there a way to have Excel automatically pull that info from the web page maybe each time the sheet is opened?  That way I could make it a online doc and people could check the scores whenever they wanted?  Right now, I send out updates every hour during the rounds.  I've tried to send the spreadsheet to people and give them instructions on how to copy the data in, but for some reason they all think it's too complicated.  That baffles me, but whatever.  Just thought if it could pull data automatically, it would be a godsend.  

Side note about my ability:  I'm about a 8.5 to 9 in Excel, but not including Macros.  So if it includes using Macros, you might have to dumb down the instructions a little for me. 

TIA
TIS

Have you tried using the Web Query function (Data Ribbon)?  It's going to depend on how the website is laid out. 

You could probably find an API to reference in the Web Query which I'm guessing would be a lot cleaner than trying to parse CBS.

 

[SaintsInDome2006]

Alllright, since this seems to work I thought I might just keep shooting to see if there are any thoughts on this one...

I have a Word doc that is enumerated (so 1. Emmitt Smith, 2. OJ Simpson, 3. Chuck Muncie, etc,)...

...is there a simple way I can convert that into an XL without doing copy/paste manually for each one? Perhaps turn it into a Word chart (ie with cells) then go from there? Or am I just dreaming of this as something that would save me a load of time and trouble???

Thanks.

 

[bryhamm]

Should be able to just copy and paste as values into Excel, then do a column to text delimination looking for either the space or the period after the number.

 

[Ned]

Yup, hard to say without seeing the doc. It’s going to totally depend on the formatting. I would try @bryhamm suggestion. If you can’t find a common delimeter, you could try fixed format and try to splice it yourself. 

 

[Inhiding]

Im putting together a worksheet of some Tshirts we are pre-ordering and I have all the info Customer Name, Phone, Address, Men/Women shirt, Color, Size and Number.  Off to the side i want the info Men or Womens shirt, Color, Size and number to populate a summary table.  Does that make sense?  that way i can just take a quick look and make sure that what I order from the manufacturer has at least what I have presold.  When the order comes in a have all the other data in place and can mail out orders.

any help?  I am not an excel person at all... thanks in advance

 

[Thorpe]

Im putting together a worksheet of some Tshirts we are pre-ordering and I have all the info Customer Name, Phone, Address, Men/Women shirt, Color, Size and Number.  Off to the side i want the info Men or Womens shirt, Color, Size and number to populate a summary table.  Does that make sense?  that way i can just take a quick look and make sure that what I order from the manufacturer has at least what I have presold.  When the order comes in a have all the other data in place and can mail out orders.

any help?  I am not an excel person at all... thanks in advance

There are at least two options

1.  You can add Subtotals (on the Data tab).  You should make a copy of the sheet and work on that.  

2.  You could do formulas - probably SUMIF.  

1 is probably easier to setup but you need to do it every time you want to calculate it.  2 would update as the detail cells are updated.

 

[Ned]

Im putting together a worksheet of some Tshirts we are pre-ordering and I have all the info Customer Name, Phone, Address, Men/Women shirt, Color, Size and Number.  Off to the side i want the info Men or Womens shirt, Color, Size and number to populate a summary table.  Does that make sense?  that way i can just take a quick look and make sure that what I order from the manufacturer has at least what I have presold.  When the order comes in a have all the other data in place and can mail out orders.

any help?  I am not an excel person at all... thanks in advance

There are at least two options

1.  You can add Subtotals (on the Data tab).  You should make a copy of the sheet and work on that.  

2.  You could do formulas - probably SUMIF.  

1 is probably easier to setup but you need to do it every time you want to calculate it.  2 would update as the detail cells are updated.

Subtotals might get confusing if he's not an excel guy.  The best option, IMO, is a pivot table, but that might be a little too confusing also.

SUMIF is probably the most straight fwd.

Example:

       A        B       C       D       E      F     G     H   I     J        K
1  Cust Name  Phone  Address  Gender  Color  Size  Number         Category  Count
2                                                                 Men       =SUMIF(D:D,J2,G:G)
3                                                                 Women     =SUMIF(D:D,J3,G:G)
4                                                                 Color     =SUMIF(E:E,J4,G:G)
5                                                                 Size      =SUMIF(F:F,J5,G:G)

 

[acarey50]

Im putting together a worksheet of some Tshirts we are pre-ordering and I have all the info Customer Name, Phone, Address, Men/Women shirt, Color, Size and Number.  Off to the side i want the info Men or Womens shirt, Color, Size and number to populate a summary table.  Does that make sense?  that way i can just take a quick look and make sure that what I order from the manufacturer has at least what I have presold.  When the order comes in a have all the other data in place and can mail out orders.

any help?  I am not an excel person at all... thanks in advance

Pivot Table is probably the easiest - you'd just put the Men/Women field, Color and Size fields in the row section, and the number in the data (assuming number is the quantity ordered)

Very simple, it's exactly how I do the summary orders for jerseys for a basketball league I help out with.

This is assuming you need a summary that gives each combination of Gender, Size and Color.

 

[Inhiding]

There are at least two options

1.  You can add Subtotals (on the Data tab).  You should make a copy of the sheet and work on that.  

2.  You could do formulas - probably SUMIF.  

1 is probably easier to setup but you need to do it every time you want to calculate it.  2 would update as the detail cells are updated.

Subtotals might get confusing if he's not an excel guy.  The best option, IMO, is a pivot table, but that might be a little too confusing also.

SUMIF is probably the most straight fwd.

Pivot Table is probably the easiest - you'd just put the Men/Women field, Color and Size fields in the row section, and the number in the data (assuming number is the quantity ordered)

Very simple, it's exactly how I do the summary orders for jerseys for a basketball league I help out with.

This is assuming you need a summary that gives each combination of Gender, Size and Color.

I actually did a pivot table!  and it worked.  Took me a bit longer than it should have but i got it and a cursory understanding of how the work.  thanks @acarey50 @Ned and @Thorpe for the help and suggestions.  i did the table and it took me a bit to figure out where the fields and data went and how it flowed into the table

 

[acarey50]

I actually did a pivot table!  and it worked.  Took me a bit longer than it should have but i got it and a cursory understanding of how the work.  thanks @acarey50 @Ned and @Thorpe for the help and suggestions.  i did the table and it took me a bit to figure out where the fields and data went and how it flowed into the table

Glad it worked out for you

 

[SaintsInDome2006]

Greetings helpful XL geniuses. Query:

- Is there a way to extract all names in a filtered column?

So if you have 1500 rows, and col. C has say 233 names that are showing x times each, how can I get a single list of just the 233 names? Is that possible, without manually hammering out the list?

Thanks!

 

[MTskibum]

Greetings helpful XL geniuses. Query:

- Is there a way to extract all names in a filtered column?

So if you have 1500 rows, and col. C has say 233 names that are showing x times each, how can I get a single list of just the 233 names? Is that possible, without manually hammering out the list?

Thanks!

Click on Data, then click on the remove duplicates with that column highlighted. It might be better to copy/paste them to a different tab first. I put a screenshot in the below link.

https://twitter.com/FishSkiand/status/1255218033162956804

 

[SaintsInDome2006]

Click on Data, then click on the remove duplicates with that column highlighted. It might be better to copy/paste them to a different tab first. I put a screenshot in the below link.

https://twitter.com/FishSkiand/status/1255218033162956804

MT, this is really awesome, thanks a lot. - SID 

 

[Dan Lambskin]

Is there an easy way to transpose a bunch of data that’s in a random grouping

for example I have 2 columns with data, then a bunch of additional data underneath that I need in column C D E, etc

but once group might have 3 rows, the next 20

ive just been doing brute force copy paste transpose and then delete what i transposed  over 

Example

https://docs.google.com/spreadsheets/d/13QEwEf-GnYWs8dfs-y1wFFENDX5HD1_NJbbYPZxjPqc/edit

 

[Thorpe]

Dan, google "excel stack multiple columns".  This will put the data into 1 column for you, then you can paste/transpose.  Or you can alter the code to what you need.

There are a few options for this, none are great IMO

 

[Chemical X]

appreciate the help here....

wife does real estate work and she is working on a pivot table that has multiple years.  she has multiple properties in each year and each property has a dollar value associated with it.  when she selects a year and views the properties, she’d like it to sort the properties by high dollar value to low dollar value.....for each year.  any ideas of a formula?

 

[acarey50]

This wouldn't be a formula, but if it's in a pivottable, you would just need to set the sort to be by property value - kind of depends on how she is setting up the pivot table and what other things she is looking to be able to do (which also determines if a pivot table is the best feature to use for what she is looking to do)

 

[bryhamm]

Greetings helpful XL geniuses. Query:

- Is there a way to extract all names in a filtered column?

So if you have 1500 rows, and col. C has say 233 names that are showing x times each, how can I get a single list of just the 233 names? Is that possible, without manually hammering out the list?

Thanks!

use a pivot table

 

[cubd8]

Existing Formula:

The below logic outputting the month value (example: June-20) if it hits any of the conditions below, else N/A
Currently, for the red logic only, if there is a date in the current month and column DC2 = 'C', it also is outputting that date (June-20). However, it is also doing so for June dates that are prior to today.

How would I update these to only evaluate those dates that are today AND within the current month?

=IF(BO2="A",TEXT(Z2,"mmmm-yy"),
IF(AND(BO2="B",MONTH(O2)=MONTH(TODAY())),TEXT(O2,"mmmm-yy"),
IF(AND(D2="C",MONTH(AT2)=MONTH(TODAY())),TEXT(AT2,"mmmm-yy"),
IF(AND(BO2="D",MONTH(DA2)=MONTH(TODAY())),TEXT(DA2,"mmmm-yy"),
IF(AND(OR(BO2="E",BO2="Fs"),MONTH(AT2)<>MONTH(TODAY())),"N/A",
IF(AND(OR(BO2="E",BO2="F"),OR(MONTH(AT2)=MONTH(TODAY()),MONTH(O2)=MONTH(TODAY()))),TEXT(TODAY(),"mmmm-yy"),"N/A"))))))

 

[Chemical X]

could use a favor here....

column A has numbers.  wife would like a formula in column b to say;

if A is  <7,000 B says Low, between 7,001-15,000 medium and over 15,000 high.

we can’t crack it.  thx

 

[Chemical X]

A little easier to read and follow:

=IFS(A1<7000, "Low", A1>15000, "High", TRUE, "Medium")

Literally: "If it's less than 7000 say Low, if it's more than 15000 say High, otherwise, say Medium"

can you add N/A if 0 to the above?  also, is it ifs or if?

 

[acarey50]

can you add N/A if 0 to the above?  also, is it ifs or if?

Should be able to do it as :  =IFS(A1=0, "N/A", A1<7000, "Low", A1>15000, "High", TRUE, "Medium")

For the above context, IFS is the proper formula. For the earlier nested formula @Walking Boot provided, IF was the correct version.

I believe the IFS statement is the easiest way for this.

There are also some other ways to do this with a min/max table. So for example, if you expect the ranges you consider to be low, medium, high to possible change, or you might be adding in additional range names, you could consider something like that as well.

 

[Chemical X]

Should be able to do it as :  =IFS(A1=0, "N/A", A1<7000, "Low", A1>15000, "High", TRUE, "Medium")

For the above context, IFS is the proper formula. For the earlier nested formula @Walking Boot provided, IF was the correct version.

I believe the IFS statement is the easiest way for this.

There are also some other ways to do this with a min/max table. So for example, if you expect the ranges you consider to be low, medium, high to possible change, or you might be adding in additional range names, you could consider something like that as well.

thx, will give it a try.

 

[acarey50]

Actually, I think instead of using 6999.9 you can use the >= operator in the formula. Not knowing the source data, this would account for any unlikely scenario where the value is between 6999.9 and 7000.0

That would make the IF formula:

=IF(A1=0, "NA",IF(AND(A1>0, A1<7000),"Low",(IF(AND(A1>=7000,A1<15000),"Medium","High"))))

 

[Chemical X]

"IFS" is like a super-powered "IF", which is what makes the second example easier to read. 

The first one I gave is literally "If A1 is less than 7000, say Low, otherwise, if A1 is between 7001 and 15000, say High, otherwise, say Medium". This is called a nested IF, there's one IF inside of another IF. The more you want to add, the more confusing it gets.

For example, now that you want a 4th condition, the IF statement looks like this:

=IF(A1=0, "NA",IF(AND(A1>0, A1<7000),"Low",(IF(AND(A1>6999.9,A1<15000),"Medium","High"))))

(PS, my first example was incorrect for the edge cases 7000 and 7001. I should have used 6999.9 instead of 7000)

This reads as "If A1 is 0, say NA otherwise, if A1 is greater than 0 but less than 7000, say Low, otherwise, if A1 is more than 6999.9 and less than 15000, say Medium, otherwise, say High"

The second answer, using IFS, can now easily be modified like this:

=IFS(A1=0, "NA", A1<7000, "Low", A1>15000, "High", TRUE, "Medium")

I just added the first pair of instructions "If A1 is Zero, say NA" in the first evaluation position. Note that in this case, Excel helps us by stopping as soon as a condition is true: even though A1 is also less than 7000 when it is equal to zero, since it found the first case is true, it stops evaluation, returns the text 'NA', and exits.

appreciate the replies.  first one didn’t work.  gonna try this after a quick meal.  many thx.  i will advice if it worked.

 

[Chemical X]

Actually, I think instead of using 6999.9 you can use the >= operator in the formula. Not knowing the source data, this would account for any unlikely scenario where the value is between 6999.9 and 7000.0

That would make the IF formula:

=IF(A1=0, "NA",IF(AND(A1>0, A1<7000),"Low",(IF(AND(A1>=7000,A1<15000),"Medium","High"))))

so, this is the winning formula!!  i owe you something from the iWorld!

 

[Chemical X]

I always forget how to format a proper less-than-or-equal-to in Excel. Unfortunately it doesn't just simply recognize the characters ≤ or ≥

acarey’s formula worked, i didn’t get to use yours, though i assume it would’ve gone thru too.  honestly, many thx for taking the time to think it thru.

 

[acarey50]

so, this is the winning formula!!  i owe you something from the iWorld!

I just leveraged the work @Walking Boot did and made a minor modification, just like I do with google and most of my work related questions.