Adding 3 variable formula to a cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm looking for a 3 variable formula for a single cell using figures from 8
cells in a column for Exel 2003. Basically, totalling variable fees for 8
different cells. If for example H1 is under (<) $10.00, then add 10.00 to the
total. If H1 is over (>) 10.01 then multiply that figure by 30% and add that
figure to total. If H1 is over 500.00 then multiply that figure by 20%.
Having all cells figures added to one cell based on previous mention variables
 
Hi Peo,
Thank you for your response and help but that didn't work. I worked out an
example of what I'm working with calculating cells starting with H24 through
H31.


figure 1 35.00 (x30%=10.50) H24
figure 2 38.99 (x30%=11.69 H25
figure 3 10.00 (10.00) H26
figure 4 400.00 (x30%=120.00) H27
figure 5 8.00 (10.00) H28
figure 6 634.00 (x20%=126.80) H29
figure 7 0.00 (10.00) H30
figure 8 0.00 (10.00) H31

SubTotal 1125.99
Misc Fee 23.51
---> **.** [Cell To Be Calculated From Figures Above = 308.99]
Other Fee 24.70
Total $1483.19

Thank You,
Kssflmail
 
One way

=SUMPRODUCT(--(H24:H31),(LOOKUP(H24:H31,{0;10.01;500.01},{0;0.3;0.2})))+SUMPRODUCT(--(H24:H31<=10),{10;10;10;10;10;10;10;10})

will return 308.997



--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




kssflmail said:
Hi Peo,
Thank you for your response and help but that didn't work. I worked out
an
example of what I'm working with calculating cells starting with H24
through
H31.


figure 1 35.00 (x30%=10.50) H24
figure 2 38.99 (x30%=11.69 H25
figure 3 10.00 (10.00) H26
figure 4 400.00 (x30%=120.00) H27
figure 5 8.00 (10.00) H28
figure 6 634.00 (x20%=126.80) H29
figure 7 0.00 (10.00) H30
figure 8 0.00 (10.00) H31

SubTotal 1125.99
Misc Fee 23.51
---> **.** [Cell To Be Calculated From Figures Above = 308.99]
Other Fee 24.70
Total $1483.19

Thank You,
Kssflmail

--------------------------------------------------------




Peo Sjoblom said:
One possible way

=IF(H1<=10,H1+10,IF(AND(H1>10,H1<=500),H1*1.3,H1*1.2))

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon
Click to expand...
Click to expand...
 
Back
Top