Excel formula amendment - tiny change :)

Dolby

Dolby

Honorary Master
Joined
Jan 31, 2005
Messages
39,123
Reaction score
6,138
Hi,

I basically have a volume based price list >

0-30 = R10.00
31-100 = R9.00
100-500 = R8.00

Cell B19 is where I enter the QTY and it looks at what bracket it is and pulls the price using the below formula.

=IF(B19<=30;Pricelist!B34;IF('7493'!B19<=100;Pricelist!C34;IF('7493'!B19<=500;Pricelist!D34;IF(B19<=1000;Pricelist!E34;IF(B19>1000;Pricelist!F34;0)))))

But if it's 0 - how do I make it pull up R0.00 ?
Basically if B19 = '0', then make cell '0'

Right now '0' is pulling the under 30 rule
 
Dolby said:
Hi,

I basically have a volume based price list >

0-30 = R10.00
31-100 = R9.00
100-500 = R8.00

Cell B19 is where I enter the QTY and it looks at what bracket it is and pulls the price using the below formula.

=IF(B19<=30;Pricelist!B34;IF('7493'!B19<=100;Pricelist!C34;IF('7493'!B19<=500;Pricelist!D34;IF(B19<=1000;Pricelist!E34;IF(B19>1000;Pricelist!F34;0)))))

But if it's 0 - how do I make it pull up R0.00 ?
Basically if B19 = '0', then make cell '0'

Right now '0' is pulling the under 30 rule
Click to expand...
You have to run the 0 test first. And make it include all negative numbers incase some silly person puts one in. So B19<=0 should be the first one
Unlike other functions - the IF function runs in the order you place the tests.
 
I jus know how to put that in .

=IF(B19=0: then 0; B19<=30;Pricelist!B34;IF('7493'!B19<=100;Pricelist!C34;IF('7493'!B19<=500;Pricelist!D34;IF(B19<=1000;Pricelist!E34;IF(B19>1000;Pricelist!F34;0)))))

I'm looking for that in Excel terms :P
 
I think if you have Office 365 you can use a case statement which would make that formula much more manageable
 
LaraC said:
Vlookup will not account for negative values or values outside the range. :)
Click to expand...
Sure but you can sort that out with a general test to ensure invalid values cant be entered in the first place.

Does no one do this by default?
 
Last edited:
Geoff.D said:
S

Sure bur you can sort that out with a general test to ensure invalid values cant be entered in the first place.

Does no one do thisby default?
Click to expand...
What is thisby default? Is it the same as data validation in Excel? :)
 
Dolby said:
I jus know how to put that in .

=IF(B19=0: then 0; B19<=30;Pricelist!B34;IF('7493'!B19<=100;Pricelist!C34;IF('7493'!B19<=500;Pricelist!D34;IF(B19<=1000;Pricelist!E34;IF(B19>1000;Pricelist!F34;0)))))

I'm looking for that in Excel terms :p
Click to expand...
Wont it be:

=IF(B19=0; 0; B19<=30;Pricelist!B34;IF('7493'!B19<=100;Pricelist!C34;IF('7493'!B19<=500;Pricelist!D34;IF(B19<=1000;Pricelist!E34;IF(B19>1000;Pricelist!F34;0)))))
 
LaraC said:
What is thisby default? Is it the same as data validation in Excel? :)
Click to expand...
I have never allowed inputs into any SS that does not prevent invalid data entries. In the old days was cumbersome, but is easier using the validation measures now available.
Even intermediate calculation validation checks is pretty standard in my SS's.

The worst case I have ever seen was in a company that tied approvals to Quantities as well as Rand values.

Their system collapsed completely If you exceeded 100 000 or, R 100 million.
It did not throw out an error but allowed the order to go through without approval.
So it did not take long for someone to work out how to bypass the approval process, just place a very large order qtywise of value wise, and then AFTER the order is processed, amend it to what you want to order.
 
Dolby said:
I jus know how to put that in .

=IF(B19=0: then 0; B19<=30;Pricelist!B34;IF('7493'!B19<=100;Pricelist!C34;IF('7493'!B19<=500;Pricelist!D34;IF(B19<=1000;Pricelist!E34;IF(B19>1000;Pricelist!F34;0)))))

I'm looking for that in Excel terms :p
Click to expand...
Ok first thing you need to do is go to Regional settings. You have SA selected (which is correct) but our government does some strange things. You need to make the decimal separator for numbers and currency the . and not the ,.

=IF(B19<=0,0,if(B19<=30,Pricelist!B34,IF('7493'!B19<=100,Pricelist!C34,IF('7493'!B19<=500,Pricelist!D34,IF(B19<=1000,Pricelist!E34,IF(B19>1000,Pricelist!F34,0))))))
You are doing =IF(test lower and = 0, 0, if(test for the rest as you have it.....)
The rest of your function is fine - it is the regional settings that is the problem. For certain functions the ; works - but not in this. And Excel doesn't like , as the decimal separator.

@Geoff.D is right, you can use validation to remove the need for testing lower than 0 but in my experience is that people can wipe out that unless you protect the sheet. So I build it into my formulas.

1613363444559.png
https://edu.gcfglobal.org/en/excel-tips/understanding-regional-differences-in-excel/1/ You can find instructions here if you don't know how.
 
Last edited:
Dolby said:
I jus know how to put that in .

=IF(B19=0: then 0; B191000;Pricelist!F34;0)))))

I'm looking for that in Excel terms :P
Click to expand...
Basically, you test for the 0 first, if true than value is 0, else move onto next if statement

=IF(B19<=0;0;IF(B19<=30;Pricelist!B34;IF('7493'!B19<=100;Pricelist!C34;IF('7493'!B19<=500;Pricelist!D34;IF(B19<=1000;Pricelist!E34;IF(B19>1000;Pricelist!F34;0))))))

Note there is an extra end bracket at the end because we added another if
 
Last edited:
In Windows 7 & 8.x, if you set the language & regional (number format) settings to South Africa, then the default separator for currency was the full-stop, the thousands separator was a space & the formula list separator was a comma, which allowed formulas to work correctly as intended.

But, for some as yet obscure reason, in Windows 10, the South Africa settings apply the COMMA as both the decimal AND the formula list separator ( or a comma as both the 1000's separator AND the decimal, and a semi-colon for the list separator) - which throws out both number formatting AND formulas compiled in Excel on a PC that used Win 7 or 8.x.

In Win 10, go to Settings, then Time & Language, then click on the Region option - then follow the instructions below to set your punctuation symbols as directed:

1613367670958.png

Once you have completed Step 6, simply close all the settings windows, close & restart Excel, and your decimal separator will now be the full-stop, the thousands separator will be a space, and the list separator will be the comma, allowing you to compile formulas more easily from then on.

(N.B.: Make sure you apply the same settings (decimal & thousands separator) on the CURRENCY tab as well as on the NUMBER tab).
 
Last edited:
Ivan Leon said:
In Windows 7 & 8.x, if you set the language & regional (number format) settings to South Africa, then the default separator for currency was the full-stop, the thousands separator was a space & the formula list separator was a comma, which allowed formulas to work correctly as intended.

But, for some as yet obscure reason, in Windows 10, the South Africa settings apply the COMMA as both the decimal AND the formula list separator ( or a comma as both the 1000's separator AND the decimal, and a semi-colon for the list separator) - which throws out both number formatting AND formulas compiled in Excel on a PC that used Win 7 or 8.x.

In Win 10, go to Settings, then Time & Language, then click on the Region option - then follow the instructions below to set your punctuation symbols as directed:

View attachment 1020670

Once you have completed Step 6, simply close all the settings windows, close & restart Excel, and your decimal separator will now be the full-stop, the thousands separator will be a space, and the list separator will be the comma, allowing you to compile formulas more easily from then on.
Click to expand...
Don't forget the currency tab. Needs to be done there too.
 
TribbleZA said:
Ok first thing you need to do is go to Regional settings. You have SA selected (which is correct) but our government does some strange things. You need to make the decimal separator for numbers and currency the . and not the ,.

=IF(B19<=0,0,if(B19<=30,Pricelist!B34,IF('7493'!B19<=100,Pricelist!C34,IF('7493'!B19<=500,Pricelist!D34,IF(B19<=1000,Pricelist!E34,IF(B19>1000,Pricelist!F34,0))))))
You are doing =IF(test lower and = 0, 0, if(test for the rest as you have it.....)
The rest of your function is fine - it is the regional settings that is the problem. For certain functions the ; works - but not in this. And Excel doesn't like , as the decimal separator.

@Geoff.D is right, you can use conditional formatting to remove the need for testing lower than 0 but in my experience is that people can wipe out that unless you protect the sheet. So I build it into my formulas.

View attachment 1020662
https://edu.gcfglobal.org/en/excel-tips/understanding-regional-differences-in-excel/1/ You can find instructions here if you don't know how.
Click to expand...
Never use conditional formatting for data validation. It is just there to make SSs pretty or hideous depending on your point of view.
No, you either use the normal validation checks available, or, use your own formulas to makes sure data entries are valid, within ranges, etc.
And besides, the best way is to create an entry area on a sheet or a special sheet and always protect all calculation areas or sheets.
 
Geoff.D said:
Never use conditional formatting for data validation. It is just there to make SSs pretty or hideous depending on your point of view.
No, you either use the normal validation checks available, or, use your own formulas to makes sure data entries are valid, within ranges, etc.
And besides, the best way is to create an entry area on a sheet or a special sheet and always protect all calculation areas or sheets.
Click to expand...
lol my mistake - meant to write validation while doing conditional formatting on a sheet :X3:
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X