| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
only display positive values
I have a spreadsheet that forecasts based on history, unfortunately where
there have been negative sales the formula creates a negative forecast which I want to read as zero. I am using the macro below thanks Dim i As Long For i = 31 To Application.ActiveSheet.UsedRange.Rows.Count If Not Cells(i, "V").Locked Then _ Cells(i, "V").FormulaR1C1 = "=SUM(RC15:RC17)/3" Cells(i, "V").Copy If Not Cells(i, "V").Locked Then _ Cells(i, "V").PasteSpecial Paste:=xlValues |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
only display positive values
Try an IF statement in your formula:
Cells(i, "V").FormulaR1C1 = "=IF(SUM(RC15:RC17)/3<0,0,SUM(RC15:RC17)/3)" Mike F "Michael" wrote in message ... I have a spreadsheet that forecasts based on history, unfortunately where there have been negative sales the formula creates a negative forecast which I want to read as zero. I am using the macro below thanks Dim i As Long For i = 31 To Application.ActiveSheet.UsedRange.Rows.Count If Not Cells(i, "V").Locked Then _ Cells(i, "V").FormulaR1C1 = "=SUM(RC15:RC17)/3" Cells(i, "V").Copy If Not Cells(i, "V").Locked Then _ Cells(i, "V").PasteSpecial Paste:=xlValues |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
only display positive values
Cells(i, "V").FormulaR1C1 = "=IF(SUM(RC15:RC17)<0,0,SUM(RC15:RC17)</3)"
-- - K Dales "Michael" wrote: I have a spreadsheet that forecasts based on history, unfortunately where there have been negative sales the formula creates a negative forecast which I want to read as zero. I am using the macro below thanks Dim i As Long For i = 31 To Application.ActiveSheet.UsedRange.Rows.Count If Not Cells(i, "V").Locked Then _ Cells(i, "V").FormulaR1C1 = "=SUM(RC15:RC17)/3" Cells(i, "V").Copy If Not Cells(i, "V").Locked Then _ Cells(i, "V").PasteSpecial Paste:=xlValues |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Automatic display positive or negative | Excel Discussion (Misc queries) | |||
| how do i display a variance in all positive numbers? | Excel Worksheet Functions | |||
| Formula to make Negative Values Positive & Positive Values Negative? | Excel Discussion (Misc queries) | |||
| ... Count, <<< Positive Values minus Negative Values >>> ... | Excel Worksheet Functions | |||
| Multiply (C8-C4) by B10 to display positive only i.e. C8>=C4 | New Users to Excel | |||
Linear Mode
