Formula to increase a value by % based on two different types of data.

S

SMExcel

New Member
Joined
Jun 29, 2015
Messages
13
Hi All,

I am having trouble trying to work this one out. I can't figure out the formula that goes in the green cells highlighted below. In the green cells I want to increase the cost base of each property by the % from the data below, based on whether the property is allocated as a house/unit, and what postcode it belongs to. For example. Property 1's cost-base of $200k would increase by 3.5% as it's a house and has the postcode 3500. Therefore the amount in the green adjustment cell will be $7,000.

Really appreciate any feedback.

Cheers


<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"><col width="100"><col width="131"></colgroup><tbody>[TR]
[TD]Property Name[/TD]
[TD][/TD]
[TD]Postcode[/TD]
[TD]Cost Base[/TD]
[TD]Adjustment[/TD]
[TD]New Adjusted Value[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Property 1[/TD]
[TD="align: center"]House[/TD]
[TD="align: center"]3500[/TD]
[TD="align: right"]200000[/TD]
[TD="bgcolor: #93c47d"][/TD]
[TD="bgcolor: #f6b26b"][/TD]
[/TR]
[TR]
[TD]Property 2[/TD]
[TD="align: center"]Unit[/TD]
[TD="align: center"]3400[/TD]
[TD="align: right"]300000[/TD]
[TD="bgcolor: #93c47d"][/TD]
[TD="bgcolor: #f6b26b"][/TD]
[/TR]
[TR]
[TD]Proprety 3[/TD]
[TD="align: center"]House[/TD]
[TD="align: center"]3300[/TD]
[TD="align: right"]400000[/TD]
[TD="bgcolor: #93c47d"][/TD]
[TD="bgcolor: #f6b26b"][/TD]
[/TR]
</tbody>[/TABLE]

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 100px"><col width="100"><col width="100"></colgroup><tbody>[TR]
[TD]Data - Increase in Market Value

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]House[/TD]
[TD="align: right"]3500[/TD]
[TD="align: right"]3.50%[/TD]
[/TR]
[TR]
[TD]Unit[/TD]
[TD="align: right"]3500[/TD]
[TD="align: right"]2.00%[/TD]
[/TR]
[TR]
[TD]House [/TD]
[TD="align: right"]3400[/TD]
[TD="align: right"]3.00%[/TD]
[/TR]
[TR]
[TD]Unit[/TD]
[TD="align: right"]3400[/TD]
[TD="align: right"]1.25%[/TD]
[/TR]
[TR]
[TD]House [/TD]
[TD="align: right"]3300[/TD]
[TD="align: right"]3.25%[/TD]
[/TR]
[TR]
[TD]Unit[/TD]
[TD="align: right"]3300[/TD]
[TD="align: right"]1.75%[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
If you change your table, Column names Postcode, house% and unit%
you could use =vlookup(postcode, PHU table, match(propertyType, PHU table headings,0),0)

vlookup to get the right row match or hlookup to get column.
 
Upvote 0
Is this what you were after?

Excel 2016 (Windows) 32 bit
ABCDEF
Property NamePostcodeCost BaseAdjustmentNew Adjusted Value
Property 1
Property 2
Proprety 3
Data - Increase in Market Value%
House
Unit
House
Unit
House
Unit

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]House[/TD]
[TD="align: center"]3500[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]7000[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]Unit[/TD]
[TD="align: center"]3400[/TD]
[TD="align: right"]300000[/TD]
[TD="align: right"]3750[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]House[/TD]
[TD="align: center"]3300[/TD]
[TD="align: right"]400000[/TD]
[TD="align: right"]13000[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]3500[/TD]
[TD="align: right"]3,50%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]3500[/TD]
[TD="align: right"]2,00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]3400[/TD]
[TD="align: right"]3,00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]3400[/TD]
[TD="align: right"]1,25%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]3300[/TD]
[TD="align: right"]3,25%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]3300[/TD]
[TD="align: right"]1,75%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]E3[/TH]
[TD="align: left"]=D3*SUMIFS($C$10:$C$15,$A$10:$A$15,B3,$B$10:$B$15,C3)[/TD]
[/TR]
[TR]
[TH]E4[/TH]
[TD="align: left"]=D4*SUMIFS($C$10:$C$15,$A$10:$A$15,B4,$B$10:$B$15,C4)[/TD]
[/TR]
[TR]
[TH]E5[/TH]
[TD="align: left"]=D5*SUMIFS($C$10:$C$15,$A$10:$A$15,B5,$B$10:$B$15,C5)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
To nardagus' reply -

That does work for the first two properties, but when I use the formula for property 3 it comes up as 0 in E5. It also comes up as 0 when I change the house postcode to 3400 or 3300, any idea why this might be happening?
 
Last edited:
Upvote 0
nikio8 said:
If you change your table, Column names Postcode, house% and unit%
you could use =vlookup(postcode, PHU table, match(propertyType, PHU table headings,0),0)

vlookup to get the right row match or hlookup to get column.
Click to expand...

Can you please explain how the table should be layed out and i'll try work it around for the vlookup
 
Upvote 0
Hmmm. For Property3 Adjustment should be $13,000?
Then it works for me... Are you sure there is no error in a formula for this Property? Did you copy a formula from E3 to E4 and E5? If yes it should work...
 
Upvote 0
[TABLE="width: 1178"]
<tbody>[TR]
[TD][TABLE="width: 1200"]
<tbody>[TR]
[TD]Property Name[/TD]
[TD]Type[/TD]
[TD]Postcode[/TD]
[TD]Cost Base[/TD]
[TD]Adjustment[/TD]
[TD]New Adjusted Value[/TD]
[/TR]
[TR]
[TD]Property 1[/TD]
[TD]House[/TD]
[TD]3500[/TD]
[TD]200000[/TD]
[TD]=VLOOKUP(C2,$A$9:$C$11,MATCH(B2,$A$8:$C$8,0),0)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Property 2[/TD]
[TD]Unit[/TD]
[TD]3400[/TD]
[TD]300000[/TD]
[TD]=VLOOKUP(C3,$A$9:$C$11,MATCH(B3,$A$8:$C$8,0),0)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Proprety 3[/TD]
[TD]House[/TD]
[TD]3300[/TD]
[TD]400000[/TD]
[TD]=VLOOKUP(C4,$A$9:$C$11,MATCH(B4,$A$8:$C$8,0),0)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Postcode[/TD]
[TD]House[/TD]
[TD]Unit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3500[/TD]
[TD]3,50%[/TD]
[TD]2,00%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3400[/TD]
[TD]3,00%[/TD]
[TD]1,25%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3300[/TD]
[TD]3,25%[/TD]
[TD]1,75%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Unsure how to add additional vlookup into sumifs formula
Replies
3
Views
343
Peter_SSs
Peter_SSs
M
  • Question Question
Please help!! Need help with specialized formula.
Replies
3
Views
963
Eric W
Eric W
S
  • Question Question
Search a table and return rows based on criteria of 2 cells
Replies
1
Views
721
Fluff
Fluff
R
  • Question Question
Dynamic Table in Mail Merge with different page for key attribute
Replies
0
Views
1K
raushan316
R
R
  • Question Question
How to find last 12 nonzero values in a column of data
Replies
5
Views
836
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,884
Messages
6,175,171
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top