Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formulas needed .. thanks
Hi, pls advise if there's any formulas that can help to derive below
($) Sales Forecast, Estimate Rev, Actual Rev Sales Off 1 - Jan Sales Off 1 - Feb Sales Off 2 - Jan SAles Off 2 - Feb Sales Off 3 - Jan Sales Off 3 - Feb I'm trying to sum-up the Jan, Feb and Mar data for All sales offices by using the Actual Rev. If Actual Rev is blank, then use estimated Rev. If estimated rev is blank, then use sales forecast nos. Any help will be appreciated.. thanks! -- nikko |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formulas needed .. thanks
=SUM(IF(B2:B200="",A2:A200, B2:B200))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nikko" wrote in message ... Hi, pls advise if there's any formulas that can help to derive below ($) Sales Forecast, Estimate Rev, Actual Rev Sales Off 1 - Jan Sales Off 1 - Feb Sales Off 2 - Jan SAles Off 2 - Feb Sales Off 3 - Jan Sales Off 3 - Feb I'm trying to sum-up the Jan, Feb and Mar data for All sales offices by using the Actual Rev. If Actual Rev is blank, then use estimated Rev. If estimated rev is blank, then use sales forecast nos. Any help will be appreciated.. thanks! -- nikko |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formulas needed .. thanks
Hi Bob,
Say if Col B is for Actual rev, Col A is for Estimate rev, then what about the sales forecast column... -- nikko "Bob Phillips" wrote: =SUM(IF(B2:B200="",A2:A200, B2:B200)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nikko" wrote in message ... Hi, pls advise if there's any formulas that can help to derive below ($) Sales Forecast, Estimate Rev, Actual Rev Sales Off 1 - Jan Sales Off 1 - Feb Sales Off 2 - Jan SAles Off 2 - Feb Sales Off 3 - Jan Sales Off 3 - Feb I'm trying to sum-up the Jan, Feb and Mar data for All sales offices by using the Actual Rev. If Actual Rev is blank, then use estimated Rev. If estimated rev is blank, then use sales forecast nos. Any help will be appreciated.. thanks! -- nikko |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formulas needed .. thanks
Don't understand the question.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nikko" wrote in message ... Hi Bob, Say if Col B is for Actual rev, Col A is for Estimate rev, then what about the sales forecast column... -- nikko "Bob Phillips" wrote: =SUM(IF(B2:B200="",A2:A200, B2:B200)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "nikko" wrote in message ... Hi, pls advise if there's any formulas that can help to derive below ($) Sales Forecast, Estimate Rev, Actual Rev Sales Off 1 - Jan Sales Off 1 - Feb Sales Off 2 - Jan SAles Off 2 - Feb Sales Off 3 - Jan Sales Off 3 - Feb I'm trying to sum-up the Jan, Feb and Mar data for All sales offices by using the Actual Rev. If Actual Rev is blank, then use estimated Rev. If estimated rev is blank, then use sales forecast nos. Any help will be appreciated.. thanks! -- nikko |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formulas needed .. thanks
There are more elegant ways to do this
E1 = Actual D1 = Estimated C1 = Sales =IF(ISNUMBER(E1),E1,IF(ISNUMBER(D1),D1,C1)) In F1 and then use a sumproduct similiar to what Bob Phillips suggested. -- Wag more, bark less "nikko" wrote: Hi, pls advise if there's any formulas that can help to derive below ($) Sales Forecast, Estimate Rev, Actual Rev Sales Off 1 - Jan Sales Off 1 - Feb Sales Off 2 - Jan SAles Off 2 - Feb Sales Off 3 - Jan Sales Off 3 - Feb I'm trying to sum-up the Jan, Feb and Mar data for All sales offices by using the Actual Rev. If Actual Rev is blank, then use estimated Rev. If estimated rev is blank, then use sales forecast nos. Any help will be appreciated.. thanks! -- nikko |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formulas help needed | Excel Worksheet Functions | |||
IF FORMULAS NEEDED | Excel Worksheet Functions | |||
Help With Formulas Needed | Excel Worksheet Functions | |||
Help needed on formulas | New Users to Excel | |||
Tricky formulas needed | Excel Worksheet Functions |