Cell Calculation

[Guest]

I am setting up a input sheet for fees.
I have two cells side by side: one where a dollar amount will be entered
and the other with a drop menu. I need for the dollar amounts to be
calculated based on the drop down item chosen. For example, if the dollar
amount is a financed fee, I need it to autmoatically calculate in an separate
cell called financed fees.
I think this may be an IF function but I am note sure.
Can someone help me?!

 

[Guest]

In its simplest sense, an IF should work. Depends on how many choices you
need to make about the calculated fee in a single cell.

Assuming a specific cell for Finance Fee that needs to be calculated when
"Financed" is chosen from the drop down: $ amount in A1, dropdown in B1,
Finance Fee to be calculated in C1, then in C1:
=IF(B1="Financed",A1 * .01,0)
would calculate a 1% fee on amount in A1 if Financed chosen, or zero if not.

You can even nest IF statements like this:
=IF(B1="Financed",A1 * .01,IF(B1="Vehicle Loan",A1 * .015,0))
That can be carried to a maximum of 7 nested functions. But it gets
unwieldy if the rates or calculations change often and if you have a lot of
options in the drop down, then you may be better off looking at a table to
use a VLOOKUP() formula with.

 

[Guest]

In its simplest sense, an IF should work. Depends on how many choices you
need to make about the calculated fee in a single cell.

Assuming a specific cell for Finance Fee that needs to be calculated when
"Financed" is chosen from the drop down: $ amount in A1, dropdown in B1,
Finance Fee to be calculated in C1, then in C1:
=IF(B1="Financed",A1 * .01,0)
would calculate a 1% fee on amount in A1 if Financed chosen, or zero if not.

You can even nest IF statements like this:
=IF(B1="Financed",A1 * .01,IF(B1="Vehicle Loan",A1 * .015,0))
That can be carried to a maximum of 7 nested functions. But it gets
unwieldy if the rates or calculations change often and if you have a lot of
options in the drop down, then you may be better off looking at a table to
use a VLOOKUP() formula with.

 

[Guest]

I need for cell C1 to calculate the total of all fees chosen "financed" in
cell B1. So C1 will have a total number instead of a percent. What is the
function for this?

 

[Guest]

I need for cell C1 to calculate the total of all fees chosen "financed" in
cell B1. So C1 will have a total number instead of a percent. What is the
function for this?

 

[Guest]

I'll try an example to see if what I'm thinking is close to what you have?

A B C
1 50 Financed =SUMIF(B1:B8,"Financed",A1:A8) (will be 203)
2 5
3 10 Financed
4 38
5 55 Financed
6 60 Financed
7 104
8 28 Financed

Or perhaps it is laid out like this?
A B C
1 Financed =SUMIF(B11:B18,B1,A11:A18) (will be 203)
....other stuff between rows 2 and 10...
....
11 50 Financed
12 5 Not Financed
13 10 Financed
14 38 Cash
15 55 Financed
16 60 Financed
17 104 Gift
18 28 Financed

In this second one, you would choose from a list in B1 and the value in C1
would change based on matching B1 to something in B11:B18.

 

[Guest]

I'll try an example to see if what I'm thinking is close to what you have?

A B C
1 50 Financed =SUMIF(B1:B8,"Financed",A1:A8) (will be 203)
2 5
3 10 Financed
4 38
5 55 Financed
6 60 Financed
7 104
8 28 Financed

Or perhaps it is laid out like this?
A B C
1 Financed =SUMIF(B11:B18,B1,A11:A18) (will be 203)
....other stuff between rows 2 and 10...
....
11 50 Financed
12 5 Not Financed
13 10 Financed
14 38 Cash
15 55 Financed
16 60 Financed
17 104 Gift
18 28 Financed

In this second one, you would choose from a list in B1 and the value in C1
would change based on matching B1 to something in B11:B18.

 

[Guest]

That helps me. Thank you so much!

I'll try an example to see if what I'm thinking is close to what you have?

A B C
1 50 Financed =SUMIF(B1:B8,"Financed",A1:A8) (will be 203)
2 5
3 10 Financed
4 38
5 55 Financed
6 60 Financed
7 104
8 28 Financed

Or perhaps it is laid out like this?
A B C
1 Financed =SUMIF(B11:B18,B1,A11:A18) (will be 203)
...other stuff between rows 2 and 10...
...
11 50 Financed
12 5 Not Financed
13 10 Financed
14 38 Cash
15 55 Financed
16 60 Financed
17 104 Gift
18 28 Financed

In this second one, you would choose from a list in B1 and the value in C1
would change based on matching B1 to something in B11:B18.

 

[Guest]

That helps me. Thank you so much!

I'll try an example to see if what I'm thinking is close to what you have?

A B C
1 50 Financed =SUMIF(B1:B8,"Financed",A1:A8) (will be 203)
2 5
3 10 Financed
4 38
5 55 Financed
6 60 Financed
7 104
8 28 Financed

Or perhaps it is laid out like this?
A B C
1 Financed =SUMIF(B11:B18,B1,A11:A18) (will be 203)
...other stuff between rows 2 and 10...
...
11 50 Financed
12 5 Not Financed
13 10 Financed
14 38 Cash
15 55 Financed
16 60 Financed
17 104 Gift
18 28 Financed

In this second one, you would choose from a list in B1 and the value in C1
would change based on matching B1 to something in B11:B18.

 

[Guest]

Glad to have been able to help. Appreciate your letting me know that it is
helping you out. Feedback is always a good thing.

 

[Guest]

Glad to have been able to help. Appreciate your letting me know that it is
helping you out. Feedback is always a good thing.