Excel formula amendment - tiny change :)

It is before 12 and I have not had coffee yet. By Brain just left orbit at the sight of the first 'IF"
 
Just use the lookup function and be done with it. This is after all why the lookup function exists.
 
As to Ivan's long post about win 10 ,7 etc, that is all about people fiddling with settings out of context and failing miserably in setting their sw for the unit standards applicable to South Africa.
Win 10 has simply washed its hands of responsibility because people just don't bother to ensure they adhere to the SI standards applicable to their countries.
It is a simple matter to first ensure YOU decide what standard you are going to work according to, set the OS accordingly, and then ensure all sw is corrected to be consistent.
 
Dolby said:
Hi,
Click to expand...
VLookup goes in cell E1
Price you use Name Manager under Formulas tab
1613403230538.png
EDIT: Updated screen shot
To add more just use the starting quantity and the price. i.e. if you wanted to charge R7.00 for 500 + then add 500 in cell A5 and R7.00 in cell B5. Make sure you update Price in Name Manager if you go below A6:B6

Edit #2: Ooops should be TRUE not FALSE at the end (or leave it out it will default to TRUE). My bad.
=VLOOKUP(D1,Price,1)
 
Last edited:
Dairyfarmer said:
VLookup goes in cell E1
Price you use Name Manager under Formulas tab
View attachment 1021006
EDIT: Updated screen shot
To add more just use the starting quantity and the price. i.e. if you wanted to charge R7.00 for 500 + then add 500 in cell A5 and R7.00 in cell B5. Make sure you update Price in Name Manager if you go below A6:B6

Edit #2: Ooops should be TRUE not FALSE at the end (or leave it out it will default to TRUE). My bad.
=VLOOKUP(D1,Price,1)
Click to expand...
Why do you have blank columns in your named range? Blank columns are always bad. I have been training Excel for many years and I must admit that I have never seen a formula structure like this. Would love to know more about it. I assume it works but it looks complicated. Why are you bringing back the column you are searching and not the actual price ? And you are doing an approximate search - not an exact one. This has me very confused. I would love it if you would explain it.
 
TribbleZA said:
Why do you have blank columns in your named range? Blank columns are always bad. I have been training Excel for many years and I must admit that I have never seen a formula structure like this. Would love to know more about it. I assume it works but it looks complicated. Why are you bringing back the column you are searching and not the actual price ? And you are doing an approximate search - not an exact one. This has me very confused. I would love it if you would explain it.
Click to expand...
You can leave space to add more rows. Previously VLookup only worked if you sorted the columns (edit: when using FALSE) , but they eventually fixed that. This way you don't have to keep updating Name Manager.

If you use False (exact search), see my example columns G and H, you will get errors if the cell you are looking up doesn't exist. So you would have to add a price for every iteration 0 to 500. using approximant match it takes the closest match downwards. So 30, 29,....1 return what is in 1 not 31.

EDIT: Sorry I forgot to mention that using TRUE (or leaving it out) the range needs to be sorted. Unsorted only works for FALSE.
 
Last edited:
Dairyfarmer said:
You can leave space to add more rows. Previously VLookup only worked if you sorted the columns, but they eventually fixed that. This way you don't have to keep updating Name Manager.

If you use False (exact search), see my example columns G and H, you will get errors if the cell you are looking up doesn't exist. So you would have to add a price for every iteration 0 to 500. using approximant match it takes the closest match downwards. So 30, 29,....1 return what is in 1 not 31.
Click to expand...
The IF function is so much easier than this
1613551529407.png
I used absolutes as I assumed you would need to copy the formula down. If not - you don't need them.
 
TribbleZA said:
Why do you have blank columns in your named range? Blank columns are always bad. I have been training Excel for many years and I must admit that I have never seen a formula structure like this. Would love to know more about it. I assume it works but it looks complicated. Why are you bringing back the column you are searching and not the actual price ? And you are doing an approximate search - not an exact one. This has me very confused. I would love it if you would explain it.
Click to expand...
There is not a space in the lookup table. It consists of eight cells A1:B4. :)

It is actually a cool way to accomplish the task.
 
LaraC said:
There is not a space in the lookup table. It consists of eight cells A1:B4. :)

It is actually a cool way to accomplish the task.
Click to expand...
That is why I posted it! I still think though that it is rather overkill for a simple application of the basic lookup function/s.
 
Using =IF or =IFS is okay if you're only dealing with 3 or 4 cases, but it's going to become a real schlep to scale up to more cases.

=VLOOKUP(B1, Table1, 1, TRUE) or =LOOKUP(B1, Table1[Qty], Table1[Price]) with an =IFERROR around it (or 0 > x >= 500 validation on B1) looks a bit cleaner to me.

Edit: and for those of you who have access to XLOOKUP, it has the IFERROR built in, and you can explicitly specify the match mode to choose next smallest item:

=XLOOKUP(B1, Table1[Qty], Table1[Qty], "Error", -1)
 
TribbleZA said:
The IF function is so much easier than this
View attachment 1022008
I used absolutes as I assumed you would need to copy the formula down. If not - you don't need them.
Click to expand...
I have nested if statements with like 15 columns. Some are if(and(bleh, blah)
Need to have a better way of doing this in the formulas, easily leads to logical errors

Will be changing to vba and "select case" at some stage, a lot neater, but didn't really want a macro enabled sheet
 
Hamish McPanji said:
I have nested if statements with like 15 columns. Some are if(and(bleh, blah)
Need to have a better way of doing this in the formulas, easily leads to logical errors

Will be changing to vba and "select case" at some stage, a lot neater, but didn't really want a macro enabled sheet
Click to expand...
The absolute worst SS around at the moment is the official load shedding schedules used by Eskom.
I produced my own version of theirs in a workbook, less than 100 kB size. Go and look at theirs and the size of it to get an idea of something developed by someone with excel diarrhea.
 
TribbleZA said:
The IF function is so much easier than this
View attachment 1022008
I used absolutes as I assumed you would need to copy the formula down. If not - you don't need them.
Click to expand...
Nested IF's is such a PITA like this old chestnut I still use (too lazy to update it)
Code: 
=B23 & IF(B23>0," ","") & C23 & IF(C23>0," ","")& D23 & IF(D23>0," ","") & PROPER(E23) & IF(A23="","",IF(B23>0," ",""))&IF(A23="","",IF(IFERROR(VLOOKUP(A23,Dry,1,FALSE),"")=A23," DRY OFF",""))
 
Last edited:
1613575716591.png
Red is sorted by E.
Green is sorted by D.
See the difference between TRUE and FALSE when sorting.

BLUE is A1:B10 in Name Manager
RED is D1:E10 in Name Manager
GREEN is D121:E21 in Name Manager
 
Dairyfarmer said:
Nested IF's is such a PITA like this old chestnut I still use (too lazy to update it)
Code: 
=B23 & IF(B23>0," ","") & C23 & IF(C23>0," ","")& D23 & IF(D23>0," ","") & PROPER(E23) & IF(A23="","",IF(B23>0," ",""))&IF(A23="","",IF(IFERROR(VLOOKUP(A23,Dry,1,FALSE),"")=A23," DRY OFF",""))
Click to expand...
I use isblank(A23)
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X