Sum a license total, then vlookup to determine training cost

  • Thread starter Thread starter rbrown999
  • Start date Start date
R

rbrown999

I'm building a spreadsheet that captures product costs and sums it at the bottom. Based on that, there will be a training component. So:

Product Total
50000 to 100000 the training component will be 7500
100001 to 250000 the training component will be 17500
and so on.

I have the product ranges in cells A31 through B35 and the training costs in the corresponding C31:C35

The product totals are in cells D22:D24

So I'm looking for help building a formula that sums D22:D24 then takes that number, identifies where it is in the range of A31:B35, then vlookups across to enter the amount that's going to be in C31:C35

TIA
 
I'm building a spreadsheet that captures product costs and sums it at
the bottom. Based on that, there will be a training component. So:

Product Total
50000 to 100000 the training component will be 7500
100001 to 250000 the training component will be 17500
and so on.

I have the product ranges in cells A31 through B35 and the training
costs in the corresponding C31:C35

The product totals are in cells D22:D24

So I'm looking for help building a formula that sums D22:D24 then
takes that number, identifies where it is in the range of A31:B35,
then vlookups across to enter the amount that's going to be in
C31:C35

TIA
Click to expand...

=IF(ProductTotal>100000,17500,7500)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
=IF(ProductTotal>100000,17500,7500)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
Click to expand...

Thanks! Poor documentation on my part. To be more specific:

Cell D22 has the total price of one kind of product.
Cell D23 has the total price of another kind of product.
Cell D24 has the total price of another kind of product.

AND the training amount in column C is tied to the sum of cells D22:D24 like:

A B C
28
29
30 $50,000 $100,000 $7,500
31 $100,001 $250,000 $17,500
32 $250,001 $500,000 $37,500
33 $500,001 $1,000,000 $75,000
34 $1,000,000 $99,999,999 $125,000
35
 
Sorry.., that's not what you asked for. Here's what I use to assign
grades in my student grades manager app...

=IF(TotalMark<>"",HLOOKUP(TotalMark,GradesTable,3),"")

...where 'GradesTable' is a horizontal named range containing letter
GPAs and grades, and average ranges in row1 for lookup. The above
formula returns the letter grade to the 'Grade' column of the summary
section. The GPA column refs row 2 of the table.

The lookup ranges are...

0 50 55 60 65 70 75 80 85 90

...for the sample user file I'm looking at. In your case your lookup
table is a vertical range and so the only diff for you would be to use
VLOOKUP and specify col 2 as the position to return data from.
(Assuming Product Total ranges in col1, costs in col2 of your table)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
See my latest response. You're explanation was fine.., I just
misunderstood the scenario 1st reply but 'got it' after rereading!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
typo...

...where 'GradesTable' is a horizontal named range containing GPAs and
letter grades, and average ranges in row1 for lookup.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi,

Am Wed, 11 Feb 2015 07:59:36 -0800 (PST) schrieb (e-mail address removed):
A B C
28
29
30 $50,000 $100,000 $7,500
31 $100,001 $250,000 $17,500
32 $250,001 $500,000 $37,500
33 $500,001 $1,000,000 $75,000
34 $1,000,000 $99,999,999 $125,000
Click to expand...

in E22 try:
=IF(D22>=50000,VLOOKUP(D22,$A$30:$B$34,3,1),"")
Or delete column B because it is not necessary and change the formula
to:
=IF(D22>=50000,VLOOKUP(D22,$A$30:$B$34,2,1),"")


Regards
Claus B.
 
Thanks! Poor documentation on my part. To be more specific:

Cell D22 has the total price of one kind of product.
Cell D23 has the total price of another kind of product.
Cell D24 has the total price of another kind of product.

AND the training amount in column C is tied to the sum of cells
D22:D24 like:

A B C
28
29
30 $50,000 $100,000 $7,500
31 $100,001 $250,000 $17,500
32 $250,001 $500,000 $37,500
33 $500,001 $1,000,000 $75,000
34 $1,000,000 $99,999,999 $125,000
35
Click to expand...

There's a flaw in the structure of your data! You can't use the same
value for the max of one range AND the min for the next as exampled by
the last 2 entries. Also, you only need 1 col for the ranges...

50000 7500
100001 17500
250001 37500
500001 75000
1000001 125000

...will suffice. Name your table something meaningful like
"TrainingCosts" and give it local scope (sheet level). In my example
formula, "TotalMark" is a col-absolute, row-relative local scope
defined name that refs the "Total Mark" col in the summary section of
the worksheet. In your case the equivalent ref IMO should be
"ProductTotal".

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
For clarity, assuming your lookup table is structured as I described,
this would be the formula...

=IF(ProductTotal<>"",VLOOKUP(ProductTotal,TrainingCosts,2),"")

...using the defined names I suggested.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Or perhaps...

=IF(ProductTotal>=50000,VLOOKUP(ProductTotal,TrainingCosts,2),"")

...so totals show empty for values less than 50000.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
For clarity, assuming your lookup table is structured as I described,
this would be the formula...

=IF(ProductTotal<>"",VLOOKUP(ProductTotal,TrainingCosts,2),"")

..using the defined names I suggested.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
Click to expand...

That did it, thanks!!
 
Back
Top