SOLVED

# need help on conditional if formulas

Occasional Contributor

# need help on conditional if formulas

Trying to insert formulas for spreadsheet below

 RATE 1st \$100,000 RATE next \$400,000 RATE next \$2,000,000 RATE next \$2,500,000 RATE next \$2,500,000 RATE over \$7,500,000 RATE 2.500% 1.500% 1.000% 0.075% 0.070% 0.069% \$100,000 \$400,000 \$2,000,000 \$2,500,000 \$2,500,000 CONTRACT AMOUNT \$8,000,000.00 \$2,500.00
11 Replies

# Re: need help on conditional if formulas

See the attached demo.

# Re: need help on conditional if formulas

to calculate bond rates, for each column - need correct formula(s) to enter. Thanks

# Re: need help on conditional if formulas

is there a way to enter the formula in each column, so i can add a "total" at the end?

# Re: need help on conditional if formulas

Yes, if you wish:

# Re: need help on conditional if formulas

I appreciate your help greatly. the key is items in row 1 as "next" for each column?
Shouldn't e5 be 18,750?
Shouldn't f5 be 17,500?
Shouldn't g5 be .069% of anything over 7.5 million?

# Re: need help on conditional if formulas

0.075% of \$2,500,000 is \$1,875 and not \$18,750.

0.070% of \$2,500,000 is \$1,750 and not \$17,500.

The remaining amount over \$7,500,000 is \$500,000.

0.069% of \$500,000 is \$345.

Perhaps the percentages should have been 0.75%, 0.70% and 0.69%? The jump from 1.00% to 0.075% is very large...

# Re: need help on conditional if formulas

Thanks much, i believe you are correct.
I'll try entering that way, and see if it works.

# Re: need help on conditional if formulas

it all seems to work, except if A5 is 0, i get negative numbers in all columns, how can i fix that?
best response confirmed by rchelpforrc (Occasional Contributor)
Solution

# Re: need help on conditional if formulas

See the attached version.

# Re: need help on conditional if formulas

Hans, thanks much, you have solved my problem(s).