Excel Formula Help-If Statement

[acool]

Hi Everyone,

I am currently experiencing an issue with an if-statement formula that I am working on and was hoping to receive some help. In the current Data set shown, I am calculating a New Fee in Column C using the formula in the formula bar. After calculating this fee I am then multiplying this number by the number of returns per month to get the value in Column D. I am them summing all of these values in column D and multiplying by 12 to get the total value for the Year (D19). I was was hoping someone could help me create a formula which calculates the value for the year (D19) without using the values in Column D. I would somehow like to create a Sum product formula using Columns A, B and the formula in the formula bar to arrive at the value in D19. I'm trying to eliminate the Columns C & D and trying to fit everything into one formula. Any help would be greatly appreciated! Thank You!

 

[Fluff]

What is the formula in col D, as the values shown & your description do not match.

 

[awoohaw]

try:

=SUMPRODUCT(($B$2:$B$8),ROUND(IF($A2:$A8*$E$1>5,5,$A2:$A8*E$1),2))

 

[awoohaw]

try:

=SUMPRODUCT(($B$2:$B$8),ROUND(IF($A2:$A8*$E$1>5,5,$A2:$A8*E$1),2))

you will have to drag your cells down to complete your range formula.

i also put a rounding function in. take it out if you don't think necessary.

 

[acool]

What is the formula in col D, as the values shown & your description do not match.

The Formula in D19 is Sum (D2:D18)*12
The Formula in Row D2 is B2 *C2

What is the formula in col D, as the values shown & your description do not match.

 

[Fluff]

The Formula in Row D2 is B2 *C2

Really? Then how do you get 0*5=1.5 or any of the others?

 

[acool]

Really? Then how do you get 0*5=1.5 or any of the others?

Sorry, forgot to mention that the decimals are collapsed, and values are rounded to the nearest whole number!

 

[acool]

you will have to drag your cells down to complete your range formula.

i also put a rounding function in. take it out if you don't think necessary.

try:

=SUMPRODUCT(($B$2:$B$8),ROUND(IF($A2:$A8*$E$1>5,5,$A2:$A8*E$1),2))

For some reason, when I enter the formula above I am get a value of 202 as opposed to 2426.86. Not sure if I am doing something wrong here

 

[Beneindias]

Sorry, forgot to mention that the decimals are collapsed, and values are rounded to the nearest whole number!

I can't anderstand how, in cell D2, you have 1,5, if you're multiplying B2*C2... B2 is 0, so, D2 should be 0?

Decimals colapsed, rounded, or not...the result should be 0?

 

[Fluff]

forgot to mention that the decimals are collapsed, and values are rounded to the nearest whole number!

That does not explain the result you are showing.

Can you post your data to the board.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.