Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell formulas
I'm working on two columns of percentages - cost and profit. Occassionally,
the numbers are weird and so the percentages are either negative or extremely large. What kind of code can I write so that, if the numbers are not within a certain range,a default number is entered? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell formulas
If you refer to your percentage calculation/formula as "ypc", the formula
might look like: =IF(OR(ypc<low limit, ypchigh limit),default value,ypc) Regards Trevor "TechyTemp" wrote in message ... I'm working on two columns of percentages - cost and profit. Occassionally, the numbers are weird and so the percentages are either negative or extremely large. What kind of code can I write so that, if the numbers are not within a certain range,a default number is entered? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell formulas
Please post your formulas or code, and an example of what you mean by
"weird" and "extremely large". Are you looking for a formula or a VBA formula? Alan "The only dumb question is a question left unasked." "TechyTemp" wrote in message ... I'm working on two columns of percentages - cost and profit. Occassionally, the numbers are weird and so the percentages are either negative or extremely large. What kind of code can I write so that, if the numbers are not within a certain range,a default number is entered? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell formulas
one way:
=IF(OR(A1/B1<50%, A1/B1100%),<default number,A1/B1) An alternative: to bound the values to within 50%-100%: =MAX(50%,MIN(100%,A1/B1)) In article , TechyTemp wrote: I'm working on two columns of percentages - cost and profit. Occassionally, the numbers are weird and so the percentages are either negative or extremely large. What kind of code can I write so that, if the numbers are not within a certain range,a default number is entered? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell formulas
we may filter it also,,by drilling down the default values for the hi and low
% limits as explained by Trevor.. something like for cost_% on column A C1=if(A1< min_%,min_%,if(A1max_%,max_%,A1) and same principle for profit_% on column B.. regards -- ***** birds of the same feather flock together.. "Trevor Shuttleworth" wrote: If you refer to your percentage calculation/formula as "ypc", the formula might look like: =IF(OR(ypc<low limit, ypchigh limit),default value,ypc) Regards Trevor "TechyTemp" wrote in message ... I'm working on two columns of percentages - cost and profit. Occassionally, the numbers are weird and so the percentages are either negative or extremely large. What kind of code can I write so that, if the numbers are not within a certain range,a default number is entered? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Two formulas in on cell based on two numbers in another cell? | Excel Discussion (Misc queries) | |||
Carrying formulas over to new cell when old cell is deleted: possi | Excel Worksheet Functions | |||
How to trace a cell? Which cells use a special cell in formulas? | Excel Discussion (Misc queries) | |||
Copying formulas from cell to cell to cell to....... | Excel Discussion (Misc queries) | |||
change cell value greater than another cell value using formulas | Excel Worksheet Functions |